- 帖子
- 812
- 主題
- 7
- 精華
- 0
- 積分
- 862
- 點名
- 0
- 作業系統
- XP
- 軟體版本
- 2007
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 台中
- 註冊時間
- 2010-5-13
- 最後登錄
- 2019-10-10
|
12#
發表於 2011-4-1 12:34
| 只看該作者
本帖最後由 ANGELA 於 2011-4-1 13:32 編輯
 合併文字.zip (12.4 KB)
沒細看,以為空格是一致的,原來有那麼多種,這公式確實不適用修改如下
定義名稱: xx=LEN(問題!$A2)-LEN(SUBSTITUTE(問題!$A2," ",))
C2 =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),COLUMN(INDIRECT(LOOKUP(XX,{5,6,7,8},{"B","C","D","E"})&1))*100+1,100))
E2=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),COLUMN(INDIRECT(LOOKUP(XX,{5,6,7,8},{"C","D","E","F"})&1))*100+1,100))
F2=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),COLUMN(INDIRECT(LOOKUP(XX,{5,6,7,8},{"D","E","F","G"})&1))*100+1,100))
G2=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),COLUMN(INDIRECT(LOOKUP(XX,{5,6,7,8},{"E","F","G","H"})&1))*100+1,100))
D2 用龔版主,謝版主的方法用替代即可.
也可改成
E2=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),(MATCH(XX,{5,6,7,8},)+COLUMN(B1))*100+1,100)) 往右COPY 到G2 |
|
發表於 2011-4-1 11:30
| 
發表於 2011-4-1 14:38
| 
