如何在Excel儲存格內只取代開頭幾個字元
|
3019 |
如何在Excel儲存格內只取代開頭幾個字元 |
白曉燕：「錦子老師您好，我想請問有關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。
以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~
更多相關影片教學：請點我
更多相關文章：請點我