REPLACE 是 Excel 的30個必學函數之一，用於替換原字串為新的字串，在批量修改資料時十分好用，能幫你省下一格一格修改的時間。
本文將會用 5 分鐘的時間，完整分享 REPLACE 函數的用法，以及與 SUBSTITUTE 函數的差異。
如果你還不熟 REPLACE 函數，建議可以仔細看過一次，把 REPLACE 的用法徹底搞懂，進而提升工作效率。
那麼我們就開始吧！
REPLACE 函數用法
以下是 REPLACE 的函數說明：
REPLACE ( old_text , start_num , num_chars , new_text )
| 引數名稱 | 解釋 |
|---|---|
| old_text | 舊字串 |
| start_num | 欲替換之字元起點 |
| num_chars | 欲替換之字元數量 |
| new_text | 新字元 |
接著，我們來看看 REPLACE 的使用範例。
REPLACE 替換原字串的部分文字
假設今天蘋果公司決定把「APPLE」改成「GUAVA」。首先要做的，就是把蘋果官網中帶有「apple」的網址片段全部換成「guava」。
此時，REPLACE 函數可以這樣寫：
REPLACE ( B3 , 5 , 5 , “guava” )
| 引數名稱 | 解釋 | 範例 |
|---|---|---|
| old_text | 舊字串 | B3 |
| start_num | 欲替換之字元起點 | 5（從第 5 個字元開始替換） |
| num_chars | 欲替換之字元數量 | 5（替換 5 個字元） |
| new_text | 新字元 | “guava”（替換成「guava」） |
再來一個例子，假設有個國家的縮寫從「tw」改為「kk」，因此要將官網網址最後的「tw」也改為「kk」。
此時，REPLACE 函數可以這樣寫：
REPLACE ( B3 , 15 , 2 , “kk” )
| 引數名稱 | 解釋 | 範例 |
|---|---|---|
| old_text | 舊字串 | B3 |
| start_num | 欲替換之字元起點 | 15（從第 15 個字元開始替換） |
| num_chars | 欲替換之字元數量 | 2（替換 2 個字元） |
| new_text | 新字元 | “kk”（替換成「kk」） |
REPLACE 進階用法：指定位置插入新文字
前面介紹了用 REPLACE 替換文字的方法，接下來分享如何用 REPLACE 函數「憑空插入新文字」。
要憑空插入新文字，只要將第 3 個引數，也就是「欲替換之字元數量」設定為 0 就可以了。
假設今天蘋果有個全新的產品叫做「vision pro」，我們可以利用 REPLACE 函數憑空插入新的網址片段：
REPLACE ( B3 , 18 , 0 , “vision-pro” )
| 引數名稱 | 解釋 | 範例 |
|---|---|---|
| old_text | 舊字串 | B3 |
| start_num | 欲替換之字元起點 | 18（從第 18 個字元開始替換） |
| num_chars | 欲替換之字元數量 | 0（無需替換的原字元） |
| new_text | 新字元 | “vision-pro” |
補充說明：REPLACE 與 SUBSTITUTE 的差異
EXCEL 中，REPLACE 函數與 SUBSTITUTE 函數都能做到「調整原文本部分文字」的效果，但其實 2 個函數還是有很細微的差異的。
以下說明 REPLACE 與 SUBSTITUTE 的 2 個差異與使用情境：
- 差異一：知道文本位置 vs 不知道文本位置
- 差異二：只修改某部分 vs 全部修改
差異一：知道文本位置 vs 不知道文本位置
REPLACE 與 SUBSTITUTE 最根本的差異源自於他們的公式。
REPLACE 會根據第 2 個引數與第 3 個引數提供的「字元位置」去修改：
REPLACE ( old_text , start_num , num_chars , new_text )
而 SUBSTITUTE 會根據第 2 個引數與第 3 個引數提供的「新舊文字」去修改：
SUBSTITUTE ( text , old_text , new_text , [instance_num] )
舉例來說，像是一個網站中的所有網址都具有特定規律，如：
- https://www.apple.com/tw/
- https://www.apple.com/tw/iphone/
- https://www.apple.com/tw/watch/
不論是哪一個網址，「tw」就是在網址的 23 個字元，並占了 2 個字元長度。像這種「知道欲替換字元位置」的情況就很適合使用 REPLACE 去替換。
差異二：只修改某部分 vs 全部修改
另一個差異就要看原字串中，欲修改的文本是「只修改某部分」或是「全部修改」。
假設 APPLE 真的要換成 GUAVA ，APPLE WATCH 也會改名為 GUAVA WATCH（？）。
此時，我們若用 REPLACE 去更改底下這個網址：
- https://www.apple.com/tw/apple-watch/
會發現其實沒有辦法一次將前面的「apple」與後面的「apple」都更改成「guava」。
但如果是使用 SUBSTITUTE，就可以輕鬆設定原字串只要遇到「apple」就替換成「guava」了！
REPLACE 溫馨提醒
- 第二欄「start_num」不可以為０或負數。
- 第二欄「start_num」設定為最後一個字的下一格：代表最後一個字元的後面。
- 第三欄「num_chars」設定為 0：非用於替換，而是用於插入文本。
- 「start_num」設定為１，「num_chars」設定為０：在第一個字元前面插入文本。
REPLACE 常見錯誤
- #VALUE!：可以檢查 start_num 不能是負數或 0，num_chars不能為負數。
- #NAME?：REPLACE 函數在輸入時可能有些語法錯誤，像是 new_text 欄位的文字忘記加上雙引號（”文本”）。
- 日期格式不能直接用 REPLACE，需要先將日期轉換為文字再進行替換。