錦子老師

白曉燕：「錦子老師您好，我想請問有關Excel的取代或者語法功能，假設說我只需要在某格內「只取代開頭幾個字元」能怎麼做呢？

主要會需要這功能是因為電話資料有過多的【886912345678】這類格式的電話出現，如果說只取代886為0那當然是沒問題，但會不小心取代到某些電話剛好中間也穿插了886的號碼，我也試過=left和=right的組合，但因為不能隨便改變表格順序，還有就是0開頭和886開頭的電話號碼也太過隨機，甚至有很多資料是兩隻以上電話放在同一格資料內，導致就算=left或=right也非常不好用，雖然不能亂排序可以用篩選達成，但是超過16或20碼的電話又構成了另外一種困擾，把「『886』912『886』678」這類電話資料的「只有開頭的886」給刪除掉？麻煩解惑 ~ 感恩，謝謝！」

錦子老師：「其實只要用合併彙算功能即可以完成，操作方法如下：」

步驟1：點取B2儲存格輸入公式：

=IF(LEN(A2)<=12,0&RIGHT(A2,9),(IF(FIND(" ",A2,1)=11,LEFT(A2,FIND(" ",A2,1)-1),0& MID(A2,FIND(" ",A2,1)-9,9)))&" "&IFERROR(IF(FIND(" ",A2,FIND(" ",A2,1)+1)-FIND(" ",A2,1) =11,MID(A2,FIND(" ",A2,FIND(" ",A2,1)+1)-10,10),0&MID(A2,FIND(" ",A2,FIND(" ",A2,1)+1)-9,9)),0&RIGHT(A2,9)))&IF(LEN(A2)>25," "&0&RIGHT(A2,9),"")

再將公式複製到B3:B10儲存格。

【公式說明】

=IF(LEN(A2)<=12,0&RIGHT(A2,9),(IF(FIND(" ",A2,1)=11,LEFT(A2,FIND(" ",A2,1)-1),0& MID(A2,FIND(" ",A2,1)-9,9)))&" "&IFERROR(IF(FIND(" ",A2,FIND(" ",A2,1)+1)-FIND(" ",A2,1) =11,MID(A2,FIND(" ",A2,FIND(" ",A2,1)+1)-10,10),0&MID(A2,FIND(" ",A2,FIND(" ",A2,1)+1)-9,9)),0&RIGHT(A2,9)))&IF(LEN(A2)>25," "&0&RIGHT(A2,9),"")

公式1：FIND(" ",A2,1)

搜尋A2儲存格內容中第1個空白的位址。

公式2：FIND(" ",A2,公式1+1)

從A2儲存格內容第1個空白的位址加1位元處開始搜尋第2個空白位址。

公式3：IF(公式1=11,LEFT(A2,公式1-1),0&MID(A2,公式1-9,9))

如果公式1傳回的值等於11，則抓取A2儲存格內容左邊開始N(公式1-1)個字元，否則填入0與A2儲存格內容從公式1-9位址開始抓取9個字元。

公式4：IFERROR(IF(FIND(" ",A2,公式1+1)-FIND(" ",A2,1)=11,MID(A2,公式2)-10,10),0&MID(A2, 公式2-9,9)),0&RIGHT(A2,9)))

如果A2儲存格內容第2個空白位址減掉第1個空白位址等於11，則從A2儲存格內容第2個空白位址(公式2)減10開始抓取10個字元，否則填入0與A2儲存格內容從公式2-9位址開始抓取9個字元，如果出現錯誤值表示儲存格中只有二組數字，故填入0與A2儲存格內容從最右邊字元開始抓取9個字元。

公式5：IF(LEN(A2)>25," "&0&RIGHT(A2,9),"")

如果A2儲存格內容大於25個字元，表示有第3組數字，故填入0與A2儲存格內容從最右邊字元開始抓取9個字元。

公式6：IF(LEN(A2)<=12,0&RIGHT(A2,9),(公式3)

如果A2儲存格內容小於等於12，表示只有1組數字，故填入0與A2儲存格內容從最右邊字元開始抓取9個字元，否則執行公式3。

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

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

更多相關文章：請點我