錦子老師

薛之謙：「錦子老師您好，在A1儲存格有一串數字，儲存格內容分別是0,0,3,0,16,44,23,如何回傳第一個非0數字於儲存格B1儲存格，謝謝！」

錦子老師：「點取B1儲存格輸入公式：」

=--MID(SUBSTITUTE(SUBSTITUTE(","&A1&",",",",",,"),",0,",""),3,FIND(",,", SUBSTITUTE(SUBSTITUTE (","&A1&",",",",",,"),",0,",""),3)-3)

再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入。

【公式解說】

=--MID(SUBSTITUTE(SUBSTITUTE(","&A1&",",",",",,"),",0,",""),3,FIND(",,", SUBSTITUTE(SUBSTITUTE (","&A1&",",",",",,"),",0,",""),3)-3)

將A1儲存格的內容前後加上逗點，並將合併內容中的一個逗點變成二個逗點(SUBSTITUTE (","&A1&",",",",",,"))，再將完成的內容中是「,0,」的部份刪除(SUBSTITUTE(前公式,",0,",""))，再完成內容從第3個字元開始尋找「,,」位置，找到後減3(FIND(",,",前公式,3)-3)，算出要抓幾個字元。利用MID函數由傳回字串的第3個字元開始抓取指定字元，由於是陣列故要輸入二個-號(--MID(SUBSTITUTE(SUBSTITUTE (","&A1&",",",",",,"),",0,",""),3,前公式)。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

更多相關影片教學：請點我