REPLACE 用於替換原字串的部分位置為新的字串,在批量修改資料時十分好用,能幫你省下一格一格修改的時間。
本文將會用5分鐘的時間,用簡單的範例教會你 REPLACE 函數的完整用法,讓你能夠自由替換文本,甚至是直接在指定位置插入新文本。
因此,如果你是第一次接觸 REPLACE 函數,建議可以從頭到尾看過一次,把 REPLACE 的用法徹底搞懂,就不用遇到一次查一次公式。
那麼我們就開始吧!(趕時間的人可以利用下方目錄跳轉 👇)
REPLACE 函數用法
當我們把滑鼠移到 REPLACE 的公式時,會看到一行說明:
REPLACE(old_text, start_num, num_chars, new_text)
看起來很複雜對吧!但其實改成中文就沒那麼可怕了:
REPLACE(舊字串, 欲修改之字元起點, 欲修改之字元數量, 新字元)
- old_text:舊字串
- start_num:欲修改之字元起點
- num_chars:欲修改之字元數量
- new_text:新字元
接著,我們來看看 REPLACE 函數的用法與限制:
REPLACE 替換原字串的部分文字
假設今天蘋果高層一個卡到,決定要把商標名稱「APPLE」換成「GUAVA」。首先要做的,就是把網站帶有「apple」的網址全部換成「guava」。
此時,我們可以使用 REPLACE 函數:
=REPLACE(B3,5,5,"guava")
再來一個例子,假設蘋果官網要新增一種新語言版本的網頁,國家縮寫叫做「kk」。此時,我們就可以利用 REPLACE 函數幫助我們將「tw」改為「kk」:
=REPLACE(B3,15,2,"kk")
REPLACE 插入新文字至原字串的指定位置
前面介紹了用 REPLACE 替換文字的方法,接下來說說如何用 REPLACE 憑空插入新文字。
其實超級簡單,將 num_chars(欲修改之文字數量)設為 0 即可。
假設今天蘋果有個新產品叫做「vision pro」,我們可以利用 REPLACE 新增一個新的網址:
=REPLACE(B3,18,0,"vision-pro")
REPLACE 與 SUBSTITUTE 差異
差異一、知道文本位置 vs 不知道文本位置
REPLACE 與 SUBSTITUTE 最根本的差異源自於他們的公式:
REPLACE(old_text, start_num, num_chars, new_text) //根據你提供的「位置」去修改
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/
不論是 APPLE 的哪一個頁面,「tw」就是在網址的23個字,並占了2個字元長度,像這種知道位置的情況就很適合用 REPLACE 去替換。
差異二、只修改某部分 vs 全部修改
另一個差異就要看原字串中,欲修改的文本是「只修改某部分」或是「全部修改」。
假設 APPLE 真的要換成 GUAVA ,想當然爾,APPLE WATCH 也會改名為 GUAVA WATCH(超難聽…)。
此時,我們若用 REPLACE 去更改底下這個網址:
- https://www.apple.com/tw/apple-watch-nike/
會發現其實沒有辦法一次將前面的 apple 與後面的 apple 都更改成 guava。
但如果是使用 SUBSTITUTE,就可以設定原字串只要遇到「apple」就替換成「guava」,便能夠順利將一個好好的產品改成很難聽的名字了。
REPLACE 溫馨提醒
- 第二欄「start_num」不可以為0或負數。
- 第二欄「start_num」設定為最後一個字的下一格:代表最後一個字元的後面。
- 第三欄「num_chars」設定為 0:非用於替換,而是用於插入文本。
- 「start_num」設定為1,「num_chars」設定為0:在第一個字元前面插入文本。
REPLACE 常見錯誤
- #VALUE!:可以檢查 start_num 不能是負數或 0,num_chars不能為負數。
- #NAME?:REPLACE 函數在輸入時可能有些語法錯誤,像是 new_text 欄位的文字忘記加上雙引號(”文本”)。
- 日期格式不能直接用 REPLACE,需要先將日期轉換為文字再進行替換。