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

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

REPLACE 函數

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

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

四個參數的意義如下：

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

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

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

REPLACE 取代文字

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

SUBSTITUTE 函數

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

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

這四個參數意義如下：

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

我們將上面 REPLACE 的範例以 SUBSTITUTE 函數改寫，將 ABCDEF 的 BCD 替換為 12345：

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

SUBSTITUTE 取代文字

應用範例

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

新舊地址轉換

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

舊地址

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

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

將「縣」改為「市」

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

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

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

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

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

套用公式

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

完成新舊地址