close

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鍵。結果如下圖所示。


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()