錦子老師

錦子老師您好：

下方圖片問題!要用函數來處理...想把合併字串(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鍵。結果如下圖所示。