在 Excel 中，Substitute函數用于把一個字符或一串字符替換另一個字符或一串字符，如果被替換字符有多個相同的，不但可以把所有字符替換掉，還可以指定只替換第幾個字符。用于替換的還有Replace函數，它與Substitute函數的區別見文章末尾。

Substitute函數可與 IsText、Sum、Value、Evaluate 等組合使用，且還可以嵌套使用。例如 Evaluate + Substitute + Substitute + IsText 組合實現工程量計算，Sum + Value + Substitute 組合實現數值帶單位求和，Substitute 嵌套一次替換多個字符。

一、Excel Substitute函數的語法

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

中文表達式：Substitute(要替換部分字符的文本, 被替換文本，替換文本，[被替換文本序號])

2、說明：

A、Substitute函數用于把一個或一串新字符替換指定的舊字符，如果要替換的字符有相同的，可以用參數 Instance_Num 指定替換哪個；如果 Instance_Num 為 1，則替換第一個，其它的以此類推。如果省略 Instance_Num，則替換所有相同的字符。

B、Substitute函數不支持使用通配符問號（?）和星號（*），例如在“被替換文本”或“替換文本”中使用 * 不代表任意一個或多個字符，只代表 * 本身。

C、Substitute函數區分大小寫，例如被替換文本中有 ab 和 Ab，若被替換文本只寫 ab，則 Ab 不被替換。

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

（一）替換所有相同字符的實例

1、假如要把 D 列數字中的 kg 全部替換掉。雙擊 D2 單元格，把公式 =SUBSTITUTE(D2,"kg","") 復制到 D2，按回車，返回替換掉 kg 的數字 2685；選中 D2，把鼠標移到 D2 右下角的單元格填充柄上，鼠標變為加號后，雙擊左鍵，則 D 列剩余數字中的 kg 也被替換掉；操作過程步驟，如圖1所示：

圖1

2、公式說明：

公式 =SUBSTITUTE(D2,"kg","") 中，D2 為要替換部分字符的文本，kg 為被替換文本，"" 為替換文本，公式省略了最后一個參數“被替換文本序號”，默認替換所有的 kg；公式的意是：把 D2 中所有的 kg 用空文本 "" 替換掉。

（二）僅替換指定的相同字符實例

1、假如只替換 D 列數字中的第 2 個 kg。雙擊 D2 單元格，把公式 =SUBSTITUTE(D2,"kg","",2) 復制到 D2，按回車，返回僅替換掉第2 個 kg 的 kg2685；操作過程步驟，如圖2所示：

圖2

2、公式說明：

公式 =SUBSTITUTE(D2,"kg","",2) 與上例的公式相比只多了第四個參數 2，意思變為：用空文本 "" 替換 D2 中的第 2 個 kg，從返回結果 kg2685 可知，只有第 2 個 kg 被替換，第 1 個并未被替換；D3 和 D5、D2 一樣，其它的單元格只有一個 kg，都沒有被替換。

（三）? 和 * 不是通配符的實例

1、雙擊 B1 單元格，把公式 =SUBSTITUTE(A1,"d*","") 復制到 B1，按回車，返回替換掉 d* 的數字 78969759389231；雙擊 B2，把=SUBSTITUTE(A2,"?","0") 復制到 B2，按回車，返回把 0 替換 ? 的數值 25802765093802378；操作過程步驟，如圖3所示：

圖3

2、公式說明：

A、公式 =SUBSTITUTE(A1,"d*","") 中，被替換文本 d* 中的 * 只代表 * 本身，如果它代表任意字符，則 d 后的所有字符都被替換掉，說明Substitute函數不支持使用通配符 *。

B、公式 =SUBSTITUTE(A2,"?","0") 中的被替換字符為 ?，它也只代表本身，如果它代表任意一個字符，A2 中的第一個數字應該被替換掉。

（四）區分大小寫的替換實例

1、假如要把“word 2019, Word 2016, word 2013”的 word 替換為 excel。雙擊 A2 單元格，把公式 =SUBSTITUTE(A1,"word","excel") 復制到 A2，按回車，返回“excel 2019, Word 2016, excel 2013”；操作過程步驟，如圖4所示：

圖4

2、從替換結果可以看出，以大寫 W 開頭的 Word 并未被替換，而兩個小寫的 word 都被替換了，說明Substitute函數區分大小寫。

（五）僅替換單個數字實例

1、假如要把“20,2,23,16,36,2,28,52”中單獨的 2 替換掉，但不能替換諸如 23 中的 2。雙擊 B1 單元格，把公式 =SUBSTITUTE(A1,",2,",",") 復制到 B1，按回車，返回替換掉 2 的結果 20,23,16,36,28,52；操作過程步驟，如圖5所示：

圖5

2、公式說明：

公式 =SUBSTITUTE(A1,",2,",",") 中的被替換文本為 ",2,"，2 的前后都有逗號，這主要用于區分諸如 23 中的 2，如果僅寫 "2"，則A1 中的所有 2 都被替換掉。

三、Excel Substitute函數的應用實例

（一）Substitute函數的嵌套使用實現一次替換多個不相同字符

1、假如要把 A 列中的左右雙引號都替換掉。雙擊 D2 單元格，把公式 =SUBSTITUTE(SUBSTITUTE(A2,"“",""),"”","") 復制到 D2，按回車，A2 中的雙引號被替換掉；選中 D2，用雙擊單元格填充柄的方法替換掉剩余的單元格的雙引號；操作過程步驟，如圖6所示：

圖6

2、公式 =SUBSTITUTE(SUBSTITUTE(A2,"“",""),"”","") 說明：

A、內層的 SUBSTITUTE(A2,"“","") 用于替換左邊的雙引號（“），它返回的結果為 龍眼”。

B、則公式變為 =SUBSTITUTE("龍眼”","”","")，最后再用空格替換掉右邊的雙引號（”），A2 中左右兩邊的雙引號都被替換掉。

提示：默認情況下，Excel 單元格（包括文本和公式單元格）不顯示中文雙引號，需要把字體設置為中文字體（如“宋體”）。

（二）Sum + Value + Substitute 組合實現數值帶單位的求和

1、有一個水果銷量表，銷量后都帶單位 kg，現要對它們求和。雙擊 D8 單元格，把公式 =SUM(VALUE(SUBSTITUTE(D2:D7,"kg",""))) 復制到 D8，按 Ctrl + Shift + 回車，返回求和結果 12743；雙擊 D8，把 VALUE 改為 --，按回車，也返回一樣的結果；操作過程步驟，如圖7所示：

圖7

2、公式 =SUM(VALUE(SUBSTITUTE(D2:D7,"kg",""))) 說明：

A、公式為數組公式，所以要按 Ctrl + Shift + 回車；D2:D7 以數組形式返回 D2 至 D7 中的所有值。

B、則 SUBSTITUTE(D2:D7,"kg","") 變為 SUBSTITUTE({"2685kg";"3500kg";"1258kg";"2050kg";"1050kg";"2200kg"},"kg","")，接著，從數組中取出第一個元素 "2685kg"，然后用空文本 "" 取代里面的 kg，其它的以此類推，最后返回 {"2685";"3500";"1258";"2050";"1050";"2200"}。

C、則 VALUE(SUBSTITUTE(D2:D7,"kg","")) 變為 VALUE({"2685";"3500";"1258";"2050";"1050";"2200"})，進一步計算，用 Value 把數組中的每個元素轉為數值；-- 與 Value 的作用一樣。

D、則公式變為 =SUM(2685;3500;1258;2050;1050;2200)，最后用 Sum 對數組中的元素求和。

（三）Substitute + Substitute + IsText 組合實現工程量計算

（1）用定義名稱計算

1、假如要計算墻的體積。選中計算公式所在單元格 A2，選擇“公式”選項卡，單擊“定義”名稱，打開“新建名稱”窗口；在“名稱”后輸入 gcl，“范圍”選擇 Sheet1，把公式 =IFERROR(EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$2,"[","*ISTEXT(""["),"]","]"")")),"") 復制到“引用位置”右邊的輸入框中，單擊“確定”，則名稱定義完成；選中 B2，輸入 =gcl，按回車，返回計算結果 7.68；操作過程步驟，如圖8所示：

圖8

2、公式 =IFERROR(EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$2,"[","*ISTEXT(""["),"]","]"")")),"") 說明：

A、Sheet1!$A$2 是對工作表 Sheet1 中 A2 單元格的絕對引用，定義名稱時，單元格都需要絕對引用，否則會找到不單元格。

B、公式最里層的 SUBSTITUTE(Sheet1!$A$2,"[","*ISTEXT(""[") 用于把 A2 中的所有左中括號 [ 用 *ISTEXT(""[ 替換；這樣做的目的是用 IsText 函數判斷 A2 中數字后面的注釋是否為文本，如果是，返回 True（即 1），否則返回 False（即 0）；例如 IsText("[墻高]") 返回 True。

提示：替換半角雙引號時，需用兩個半角雙引號，例如用 *ISTEXT(""[ 替換 [，由于 [ 前要加一個半角雙引號，所以 *ISTEXT(""[ 中加了兩個半角雙引號。

C、則 SUBSTITUTE(SUBSTITUTE(Sheet1!$A$2,"[","*ISTEXT(""["),"]","]"")") 變為 SUBSTITUTE("(10.5*ISTEXT("[墻長]*3.2*ISTEXT("[墻高]-1.8*ISTEXT("[窗長]*1.6*ISTEXT("[窗高])*0.25*ISTEXT("[墻厚]","]","]"")")，接著，再用 ]"") 替換 ]。

D、則公式變為 =IFERROR(EVALUATE("(10.5*ISTEXT("[墻長]")*3.2*ISTEXT("[墻高]")-1.8*ISTEXT("[窗長]")*1.6*ISTEXT("[窗高]"))*0.25*ISTEXT("[墻厚]")"),"")，進一步計算，用 Evaluate 執行替換好的“墻體積計算公式”，由于每個 IsText 都返回 1，因此公式變為 =IFERROR(EVALUATE("(10.5*1*3.2*1-1.8*1*1.6*1)*0.25*1"),"")。

E、進一步計算，公式變為 =IFERROR(7.68,"")，由于 7.68 不是錯誤值，所以IfError函數返回 7.68；IfError函數用于錯誤判斷，如果Evaluate 返回錯誤，IfError 將返回 ""，否則返回 Evaluate 的返回值。

（2）用宏（VBA）計算

1、同樣以計算墻的體積為例。在 Excel 窗口按 Alt + F11，打開 VBA 編輯窗口，單擊“插入”，在彈出的菜單中選擇“模塊”，新建一個模塊，把以下代碼：

Public Sub Test()
[B3] = Evaluate(Application.Substitute(Application.Substitute([B2], "[", "*ISTEXT(""["), "]", "]"")"))
End Sub

復制到模塊中，單擊“運行”，在彈出的菜單中選擇“運行子過程/用戶窗體”，則計算完成并把結果 7.68 輸出到 B3 單元格，切換到Excel 窗口可以看到；操作過程步驟，如圖9所示：

圖9

2、VBA 代碼說明：

A、[B2] 意思是引用 B2 單元格，在 VBA 中引用單元格需要用中括號 [] 把它括起來。

B、在 VBA 中使用函數時，需在其前面加 Application.，否則會提示找不到函數，例如 Application.Substitute。

C、[B3] 用于輸出 Evaluate 的執行結果，因此，執行完代碼后，在 B3 中有了 7.68。

提示：以上兩個工程量計算方法，保存時，“保存類型”都需要選擇“Excel 啟用宏的工作薄”，否則下次打開無法正確執行。

四、Replace函數與Substitute函數的區別

Replace函數是用指定字符替換一定數目的字符，Substitute函數是用一個（或一串）字符替換另一個（或一串）字符；Replace函數主要用于一次替換一長串字符，Substitute函數主要用于用一個詞替換另一個詞。假如要把一個詞替換另一個詞或把一長串字符用空文本（""）替換，用兩個函數實現方法如下：

1、假如要把 Excel 替換 A1 中的 Word。雙擊 B1 單元格，把公式 =REPLACE(A1,1,4,"Excel") 復制到 B1，按回車，返回“Excel 制表技巧”；雙擊 B2，把公式 =SUBSTITUTE(A1,"Word","Excel") 復制到 B2，按回車，也返回“Excel 制表技巧”。

2、假如要把 A4 中的一長串雜亂字符用空文本替換。雙擊 B4，把公式 =REPLACE(A4,FIND("8",A4),FIND("4 函",A4)-FIND("8",A4)+2,"") 復制到 B4，按回車，返回“Excel 函數教程”；再雙擊 B5，把公式 =SUBSTITUTE(A4," 8ut43it-r*753iot9oyt5trey8345fdh4","") 復制到 B5，按回車，同樣返回“Excel 函數教程”；操作過程步驟，如圖10所示：

圖10

3、公式說明：

A、=REPLACE(A1,1,4,"Excel") 意思是，從 A1 中的第一個字符開始用 Excel 替換 4 個字符，也就是替換掉 A1 中的 Word，Word 恰好4 個字符。公式 =SUBSTITUTE(A1,"Word","Excel") 直接用 Excel 替換 A1 中的 Word；從兩個公式可以看出，后一個公式簡單一些。

B、公式 =REPLACE(A4,FIND("8",A4),FIND("4 函",A4)-FIND("8",A4)+2,"") 意思是，從 A4 中的第 7 個字符開始用空文本（""）替換掉34 個字符，恰好是 A4 中的雜亂字符加后面一個空格。

FIND("8",A4) 用于返回要替換字符串的第一個字符（即 8）在 A4 中的位置，結果為 7。

FIND("4 函",A4)-FIND("8",A4)+2 用于計算要替換字符個數，是用要替換字符串結尾字符的位置減開始字符的位置再加 2，加 2 是把要替換字符串后的空格也替換掉；FIND("4 函",A4) 用于返回要替換字符的最后一個字母在 A4 中的位置，結果為 39，之所以要查找“4 函”，是因為 A4 中不止有一個 4，避免找錯。

C、公式 =SUBSTITUTE(A4," 8ut43it-r*753iot9oyt5trey8345fdh4","") 直接用 "" 替換 " 8ut43it-r*753iot9oyt5trey8345fdh4"，公式比用Replace函數簡單，但要替換字符特別長時，很不方便寫公式。