這裡介紹如何使用 Excel 的 REPLACESUBSTITUTE 函數來自動取代文字,並提供各種實用的範例。

在 Excel 中如果想要依照特定的規則,自動替換儲存格中的某些文字,最簡單的的方式就是使用 REPLACESUBSTITUTE 這兩個字串取代函數,這兩個函數都可用來更換儲存格中部分的文字,只不過適用的時機有些不同,以下是詳細的用法介紹以及實際範例。

REPLACE 函數

REPLACE 這個函數可以依照字串的位置與長度來取代特定的文字,其用法如下:

REPLACE(原始文字,起始位置,字串長度,取代字串)

四個參數的意義如下:

  • 原始文字:要進行字串取代的原始文字。
  • 起始位置:從原始文字中的第幾個字元開始取代字串。
  • 字串長度:要被取代的原始文字長度。
  • 取代字串:將原始文字中指定的部分以這個新字串取代。

我們以範例來解釋會比較清楚,假設我們的原始文字資料為 ABCDEF,儲存於 A2 這個儲存格中,而我們想要把其中的第二個字元到第四的字元(亦即 BCD)取代為 12345,就可以這樣寫:

=REPLACE(A2,2,3,"12345")

REPLACE 取代文字

REPLACE 函數的特點就是在取代文字時,它只會考慮原始文字的位置與長度,把指定的那個文字區段換成新的文字,而不管原始文字的那個位置原來是什麼文字,如果要依據原來的文字內容來取代,可以改用 SUBSTITUTE 函數。

SUBSTITUTE 函數

SUBSTITUTE 也是用來替換文字的函數,它可以把指定的舊文字取代為新的文字,其用法如下:

SUBSTITUTE(原始文字,被取代字串,取代字串,[作用位置])

這四個參數意義如下:

  • 原始文字:要進行字串取代的原始文字。
  • 被取代字串:要被取代的字串。
  • 取代字串:將「被取代字串」替換成這個新的「取代字串」。
  • [作用位置]:若原始文字中有多個位置都符合「被取代字串」,可以用這個作用位置參數指定要把第幾個符合的「被取代字串」替換掉。若省略這個參數,就會把所有符合的地方全部都替換掉。

我們將上面 REPLACE 的範例以 SUBSTITUTE 函數改寫,將 ABCDEFBCD 替換為 12345

=SUBSTITUTE(A2,"BCD","12345")

SUBSTITUTE 取代文字

應用範例

實務上我們可以根據不同的狀況靈活運用 REPLACESUBSTITUTE 函數,以下是一些實際的應用實例。

新舊地址轉換

假設我們的 Excel 表格中有類似這樣的舊地址資料,在台南縣改為直轄市之後,「台南縣」要改為「台南市」,而所有轄下的「市」、「鎮」、「鄉」都要改為「區」,這裡我們示範如何使用 REPLACESUBSTITUTE 函數來轉換新舊地址。

舊地址

這裡我們打算寫一個公式,然後套用在所有的地址上,自動把所有的舊地址轉換為新的地址。
Step 1
首先用 SUBSTITUTE 函數將台南縣的「縣」改為「市」:

=SUBSTITUTE(A2,"縣","市")

將「縣」改為「市」

Step 2
光只有修改「縣」的部分還不構,我們還要繼續修正上面的公式,把所有的「市」、「鎮」、「鄉」都改為「區」,而在觀察資料後,我們會發現這些字都在整行地址的第六個字元,所以我們直接使用 REPLACE 把第六個字元換成「區」即可。

這個步驟是要將上一步處理好的資料,拿來繼續做第二次的替換,最直接的做法就是用公式的方式串接起來,將上一步的 SUBSTITUTE 運算公式直接放在這裡的 REPLACE 中:

=REPLACE(SUBSTITUTE(A2,"縣","市"),6,1,"區")

這樣就可以進行兩次不同的文字替換。

Step 3
將滑鼠游標移到第一個儲存格的右下角,待滑鼠變成十字符號之後往下拉,將公式套用至以下的儲存格。

套用公式

Step 4
這樣就可以自動將所有的舊地址轉換為新地址了。

完成新舊地址