最近一位負責行政工作的朋友,在協助主管整理一批新北市的舊客戶資料時遇到了幾個問題,首先舊地址都是以台北縣開頭,需要統一更新為「新北市」,其次,舊新北市轄下的29區,全都是以縣轄市、鄉、鎮來命名,也需要統一更新為「區」,如下圖所示:
不過畢竟資料繁多,實在不太可能一筆一筆作修改,因此,以下就為各位介紹一個相當方便的Excel函數-SUBSTITUTE,並為各位示範該如何透過這個函數解決上述問題。在此之前,就先來認識一下SUBSTITUTE函數的組成引數吧!
SUBSTITUTE函數的公式為「SUBSTITUTE(text,old_text,new_text,instance_num)」,以下分別說明之:
- text:指定需要被取代的原始資料字串所在的儲存格位置
- old_text:指定在text當中需要被取代的是字串中的哪些字元?
- new_text:指定「old_text」的舊字元要被取代成哪些新字元?
- instance_num:如果在「text」原始字串中含有多組需要被修改的「old_text」字元組,則這個引數可以指定「只取代其中第幾組」字元為「new_text」,其他每一組則維持不變。(如果忽略此引數,則「text」當中所有的「old_text」字元組都會被取代為「new_text」)
有了以上的基本認識,接著就一步步來看看這個SUBSTITUTE函數該如何解決前述問題:
STEP1:
首先,如果希望將A欄中所有的「台北縣」全都取代為「新北市」,可以先在B2儲存格中輸入以下公式:「=SUBSTITUTE(A2,”台北縣”,”新北市”)」,代表要將「A2」儲存格中的「台北縣」全都取代為「新北市」
結果如下,不過雖然解決了台北縣與新北市的問題,卻還是沒能解決縣轄市、鄉、鎮的問題,因此還得對原先所設定的公式再做些調整:
STEP2:
為了解決上述問題,你可以在原本的公式上再分別加上幾組SUBSTITUTE函數。首先來解決縣轄市的問題,如果希望A2當中新莊市的「市」也能夠同步被取代為「區」,那麼你可以將原本公式修改為:「=SUBSTITUTE(SUBSTITUTE(A2,”台北縣”,”新北市”),”市”,”區”)」,代表的是先透過最內層的「SUBSTITUTE(A2,”台北縣”,”新北市”)」公式將原本的台北縣取代為新北市,再將新的字串結果(新北市新莊市)當成外層需要被取代的原始字串內容(也就是「text」儲存格中的新內容),然後再跑一次外層的SUBSTITUTE函數公式:
結果如下,不過雖然新莊市成功變成新莊區了,但前面原本新北市卻也變成了新北「區」,顯然公式有誤:
STEP3:
問題其實很簡單,只要多指定前面所提到過的「instance_num」引數即可。由於在「新北市新莊市」這個字串當中包含了兩組「市」(新北市的「市」和新莊市的「市」),而我們需要取代為「區」的是第二組新莊市中的「市」,因此只要在原本的SUBSTITUTE函數公式中將「instance_num」引數設定為「2」即可,如下圖所示:
修正後的結果如下,可以看到剛剛的問題已經被解決:
STEP4:
接著,只要重複上述步驟,再新增「鄉」及「鎮」的SUBSTITUTE函數即可,完整的SUBSTITUTE函數公式為:
「=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,”台北縣”,”新北市”),”市”,”區”,2),”鄉”,”區”),”鎮”,”區”)」,如下圖所示:
而結果則如下圖所示:
STEP5:
當你完成了B2的資料更新後,接下來只要移動滑鼠到B2儲存格上,按下滑鼠左鍵點選後,就可以看到如下圖所示儲存格右下角的小黑點:
然後將你的滑鼠游標移到小黑點上,直到游標變成實心十字形狀後,接著按著滑鼠左鍵不放,往下拖拉到B18的儲存格位置後,放開滑鼠左鍵,新的地址格式就神奇的瞬間完成嘍!以上關於Excel函數SUBSTITUTE的使用方式分享給大家,希望對大家在工作上能夠有所幫助~
我堅信「均衡,才是通往成功的唯一道路」! !除了工作,還得兼顧生活品質才能讓你走得長遠、走的順遂,因此「認真玩樂,開心工作」,一向是我面對生活的態度,也是本站成立的最大宗旨。從現在開始,就讓大家跟著人生領航員獅子心一起體驗這個充滿挑戰與樂趣的職場人生,也希望能夠幫助各位從中找回你的工作熱忱。