在 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所示:
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、公式把 Instance_Num 參數設置為 2,即用小寫 e 替換 ExcEl 2016 中的第二個大寫 E。
(三)只替換單個數字
1、假如有一串數字 25,10,5,16,51,要把裡面的單個 5 替換掉,不能替換其它數字中的 5。選中 B1 單元格,把公式 =SUBSTITUTE(A1,",5,",",") 複製到 B1,按回車,則 5 被替換掉,操作過程步驟,如圖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所示:
C、公式說明:公式 =SUBSTITUTE(SUBSTITUTE(A1,"*,", ""),"#,","") 由兩個 Substitute函數嵌套組成;裡面的 SUBSTITUTE(A1,"~*", ",") 用於替換 *;外面的 Substitute函數用於替換 #,它的 Text 參數是替換 * 後的結果,即 SUBSTITUTE(A1,"*,", "") 返回的結果。
2、替換雙引號
A、假如要把數字串 「25」,「10」,「51」 中的雙引號去掉。選中 B1 單元格,把公式 =SUBSTITUTE(SUBSTITUTE(A1,"「", ""),"」","") 複製到 B1,按回車,則數字串中的左右雙引號全被去掉,操作過程步驟,如圖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所示:
2、按回車,則源文本中的括號及其中的字符串被替換為「函數」,返回 excel2016函數,如圖8所示:
3、公式說明:
1、公式 =REPLACE(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1,"函數") 由Replace函數和Find函數組成,Find函數用於找出左右括號在文本中的位置,Replace函數用於替換。
2、第一個 FIND("(",A1) 用於找出左括號在文本中的位置作為 Replace 的「開始替換位置」;FIND(")",A1) 用於找出右括號在文本中的位置,然後用右括號的位置減左括號的位置再加 1 作為 Replace 的「要查找的字符數」。