錦子老師

A1儲存格內容為：「20230713,ax,1,11.80,P2023071300208,2023-07-13,C2023071318,,,168-33,168-33,002,」，公式要怎麼寫才可以抓出11.80這個數字。

首先要將11.80這個數字前後變成不是逗號，而用其他符號取代(我是用~號)，以方便我們抓取該數值。

先將10.80前面的逗號變更，公式為：「=SUBSTITUTE(A1,",","~",3)」。

再將10.80後面的逗號變更，公式為：「=SUBSTITUTE(A1,",","~",4)」。

然後抓取這二個~號位置。

=FIND("~",SUBSTITUTE(A1,",","~",3))

=FIND("~",SUBSTITUTE(A1,",","~",4))

將二個逗號中間的資料抓取出來：從A1儲存格第14+1(不含前面逗號)個字元位置開始抓取20-14再減1(後面逗逗號本身)個字元，

=MID(A1,FIND("~",SUBSTITUTE(A1,",","~",3))+1,FIND("~",SUBSTITUTE(A1,",","~",4))-FIND("~",SUBSTITUTE(A1,",","~",3))-1)

最後怕抓出來的字串中含有空白字元，故利用TRIM函數去除其中的空白字元。

=TRIM(MID(A1,FIND("~",SUBSTITUTE(A1,",","~",3))+1,FIND("~",SUBSTITUTE(A1,",","~",4))-FIND("~",SUBSTITUTE(A1,",","~",3))-1))