Q46 |
Excel函數問題 |
||||
示範檔 |
無 |
範例檔 |
Q46.XLSX |
結果檔 |
Q46F.XLSX |
錦子老師您好:
下方圖片問題!要用函數來處理...想把合併字串(B2欄位),只有[]拆開,並把字串塞到C D E欄位去...
可以麻煩指點一下技巧嗎?而合併字串(B2欄位),最前面未來可能還會有【xxx】值,所以只取最後三個塞過去CDE欄位就OK了...,則C2,D2,E2該怎麼表示函數呢?
1.點取C2儲存格,輸入公式「=LEFT(RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW (INDIRECT("1:"&LEN($B2))),1)="[",ROW(INDIRECT("1:"&LEN($B2))),""),3)),SEARCH("]",RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),1)="[",ROW (INDIRECT("1:"&LEN($B2))),""),3)))-1)」後,按Ctrl+Shift+Enter鍵。
2.點取D2儲存格,輸入公式「=LEFT(RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW (INDIRECT("1:"&LEN($B2))),1)="[",ROW(INDIRECT("1:"&LEN($B2))),""),2)),SEARCH("]",RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),1)="[",ROW (INDIRECT("1:"&LEN($B2))),""),2)))-1)」後,按Ctrl+Shift+Enter鍵。
3.點取E2儲存格,輸入公式「=LEFT(RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW (INDIRECT("1:"&LEN($B2))),1)="[",ROW(INDIRECT("1:"&LEN($B2))),""),1)),SEARCH("]",RIGHT($B2,LEN($B2)-LARGE(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),1)="[",ROW (INDIRECT("1:"&LEN($B2))),""),1)))-1)」後,按Ctrl+Shift+Enter鍵。結果如下圖所示。
留言列表