這裡介紹如何使用 Excel 的 REPLACE
與 SUBSTITUTE
函數來自動取代文字,並提供各種實用的範例。
在 Excel 中如果想要依照特定的規則,自動替換儲存格中的某些文字,最簡單的的方式就是使用 REPLACE
與 SUBSTITUTE
這兩個字串取代函數,這兩個函數都可用來更換儲存格中部分的文字,只不過適用的時機有些不同,以下是詳細的用法介紹以及實際範例。
REPLACE
函數
REPLACE
這個函數可以依照字串的位置與長度來取代特定的文字,其用法如下:
=REPLACE(原始文字,起始位置,字串長度,取代字串)
四個參數的意義如下:
原始文字
:要進行字串取代的原始文字。起始位置
:從原始文字中的第幾個字元開始取代字串。字串長度
:要被取代的原始文字長度。取代字串
:將原始文字中指定的部分以這個新字串取代。
我們以範例來解釋會比較清楚,假設我們的原始文字資料為 ABCDEF
,儲存於 A2
這個儲存格中,而我們想要把其中的第二個字元到第四的字元(亦即 BCD
)取代為 12345
,就可以這樣寫:
=REPLACE(A2,2,3,"12345")
REPLACE
函數的特點就是在取代文字時,它只會考慮原始文字的位置與長度,把指定的那個文字區段換成新的文字,而不管原始文字的那個位置原來是什麼文字,如果要依據原來的文字內容來取代,可以改用 SUBSTITUTE
函數。
SUBSTITUTE
函數
SUBSTITUTE
也是用來替換文字的函數,它可以把指定的舊文字取代為新的文字,其用法如下:
=SUBSTITUTE(原始文字,被取代字串,取代字串,[作用位置])
這四個參數意義如下:
原始文字
:要進行字串取代的原始文字。被取代字串
:要被取代的字串。取代字串
:將「被取代字串」替換成這個新的「取代字串」。[作用位置]
:若原始文字中有多個位置都符合「被取代字串」,可以用這個作用位置參數指定要把第幾個符合的「被取代字串」替換掉。若省略這個參數,就會把所有符合的地方全部都替換掉。
我們將上面 REPLACE
的範例以 SUBSTITUTE
函數改寫,將 ABCDEF
的 BCD
替換為 12345
:
=SUBSTITUTE(A2,"BCD","12345")
應用範例
實務上我們可以根據不同的狀況靈活運用 REPLACE
與 SUBSTITUTE
函數,以下是一些實際的應用實例。
新舊地址轉換
假設我們的 Excel 表格中有類似這樣的舊地址資料,在台南縣改為直轄市之後,「台南縣」要改為「台南市」,而所有轄下的「市」、「鎮」、「鄉」都要改為「區」,這裡我們示範如何使用 REPLACE
與 SUBSTITUTE
函數來轉換新舊地址。
這裡我們打算寫一個公式,然後套用在所有的地址上,自動把所有的舊地址轉換為新的地址。
Step 1
首先用 SUBSTITUTE
函數將台南縣的「縣」改為「市」:
=SUBSTITUTE(A2,"縣","市")
Step 2
光只有修改「縣」的部分還不構,我們還要繼續修正上面的公式,把所有的「市」、「鎮」、「鄉」都改為「區」,而在觀察資料後,我們會發現這些字都在整行地址的第六個字元,所以我們直接使用 REPLACE
把第六個字元換成「區」即可。
這個步驟是要將上一步處理好的資料,拿來繼續做第二次的替換,最直接的做法就是用公式的方式串接起來,將上一步的 SUBSTITUTE
運算公式直接放在這裡的 REPLACE
中:
=REPLACE(SUBSTITUTE(A2,"縣","市"),6,1,"區")
這樣就可以進行兩次不同的文字替換。
Step 3
將滑鼠游標移到第一個儲存格的右下角,待滑鼠變成十字符號之後往下拉,將公式套用至以下的儲存格。
Step 4
這樣就可以自動將所有的舊地址轉換為新地址了。