超實用Office教學－【Excel超實用函數教學－《SUBSTITUTE 函數》用法：如何以新字元取代原字串中的指定字元】

最近一位負責行政工作的朋友，在協助主管整理一批新北市的舊客戶資料時遇到了幾個問題，首先舊地址都是以台北縣開頭，需要統一更新為「新北市」，其次，舊新北市轄下的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的使用方式分享給大家，希望對大家在工作上能夠有所幫助～