Excel Substitute函數使用方法,含嵌套一次替換多個不同字符實例

在 Excel 中，一共有兩個替換字符的函數，一個是Substitute函數，另一個是Replace函數；前者是用新字符替換舊字符，後者是用新字符替換指定字符數。Substitute函數既可以替換源文本中所有指定字符，又可以替換某個指定字符，並且還能嵌套使用以實現一次替換多個不同字符。以下是 Excel Substitute函數的使用方法，共包含5個實例，實例操作所用版本均為 Excel 2016。

一、Substitute函數語法

1、表達式：SUBSTITUTE(Text, Old_Text, New_Text, [Instance_Num])

中文表達式：Substitute(文本, 被替換文本，替換文本，[被替換文本序號])

2、說明：

Instance_Num 為可選參數，用於指定替換第幾個 Old_Text，如果省略，則替換所有 Old_Text。另外，Substitute函數不支持通配符，例如星號 "*" 不是代表任意個字符，只代表星號本身。

二、Substitute函數的使用方法及實例

(一）省略 Instance_Num 參數一次替換多個相同字符

1、假如要把「excel替換字母,數字,漢字」中的半角逗號（,)替換為全形逗號（，）。選中 B1 單元格，輸入公式 =substitute(a1,",","，")，按回車，則「excel替換字母,數字,漢字」中所有半角逗號被替換為全形逗號；操作過程步驟，如圖1所示：

圖1

2、公式說明：公式 =substitute(a1,",","，") 省略了 Instance_Num 參數，則會用 New_Text（即全形逗號） 替換文本中所有 Old_Text（即半角逗號）。

(二）指定 Instance_Num 參數一次僅替換一個字符

1、假如要替換 ExcEl 2016 中第二個大寫 E 為小寫。選中 B1 單元格，把公式 =SUBSTITUTE(A1,"E","e",2) 複製到 B1，按回車，返回 Excel 2016；操作過程步驟，如圖2所示：

圖2

2、公式把 Instance_Num 參數設置為 2，即用小寫 e 替換 ExcEl 2016 中的第二個大寫 E。

（三）只替換單個數字

1、假如有一串數字 25,10,5,16,51，要把裡面的單個 5 替換掉，不能替換其它數字中的 5。選中 B1 單元格，把公式 =SUBSTITUTE(A1,",5,",",") 複製到 B1，按回車，則 5 被替換掉，操作過程步驟，如圖3所示：

圖3

2、公式說明：公式 =SUBSTITUTE(A1,",5,",",") 把 Old_Text 參數設置為 ",5,"，主要是為了區別其它數字（如 25）中的 5，以避免把 25 中的 5 也替換掉；把 New_Text 設置為 ","，用於替換掉 5 後把 5 的前後數字分隔。

（四）Substitute函數嵌套使用，同時替換多個不同字符

1、替換通配符星號 * 和井號 #

A、假如要替換 25,*,10,#,51 中的 * 和 #。 選中 B1 單元格，把公式 =SUBSTITUTE(SUBSTITUTE(A1,"*,", ""),"#,","") 複製到 B1，如圖4所示：

B、按回車，則 25,*,10,#,51 中 * 和 # 被替換掉，如圖5所示：

圖5

C、公式說明：公式 =SUBSTITUTE(SUBSTITUTE(A1,"*,", ""),"#,","") 由兩個 Substitute函數嵌套組成；裡面的 SUBSTITUTE(A1,"~*", ",") 用於替換 *；外面的 Substitute函數用於替換 #，它的 Text 參數是替換 * 後的結果，即 SUBSTITUTE(A1,"*,", "") 返回的結果。

2、替換雙引號

A、假如要把數字串 「25」,「10」,「51」 中的雙引號去掉。選中 B1 單元格，把公式 =SUBSTITUTE(SUBSTITUTE(A1,"「", ""),"」","") 複製到 B1，按回車，則數字串中的左右雙引號全被去掉，操作過程步驟，如圖6所示：

圖6

B、公式說明：公式 =SUBSTITUTE(SUBSTITUTE(A1,"「", ""),"」","") 也由兩個 Substitute函數嵌套組成；裡面的 SUBSTITUTE(A1,"「", "") 用於去掉左雙引號，外面的 Substitute 用於去掉右邊的雙引號。

三、SubStitute函數與Replace函數的區別

SubStitute函數是用一個（或一串）字符替換另一個字符（或一串），而Replace函數是用一個（或一串）字符替換指定的字符數。如果要替換括號中的一串不確定有多少個的字符，由於SubStitute函數不支持通配符，因此無法實現替換，但用Replace函數卻能替換，方法如下：

1、假如要替換文本「excel2016（kh@ei8#9kdfk8*ksdkjd）」中的括號及其中的字符串為「函數」。把公式 =REPLACE(A1,FIND("（",A1),FIND("）",A1)-FIND("（",A1)+1,"函數") 複製到 B1 單元格，如圖7所示：

圖7

2、按回車，則源文本中的括號及其中的字符串被替換為「函數」，返回 excel2016函數，如圖8所示：

圖8

3、公式說明：

1、公式 =REPLACE(A1,FIND("（",A1),FIND("）",A1)-FIND("（",A1)+1,"函數") 由Replace函數和Find函數組成，Find函數用於找出左右括號在文本中的位置，Replace函數用於替換。

2、第一個 FIND("（",A1) 用於找出左括號在文本中的位置作為 Replace 的「開始替換位置」；FIND("）",A1) 用於找出右括號在文本中的位置，然後用右括號的位置減左括號的位置再加 1 作為 Replace 的「要查找的字符數」。