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

Substitute函數(shù)可與 IsText、Sum、Value、Evaluate 等組合使用，且還可以嵌套使用。例如 Evaluate + Substitute + Substitute + IsText 組合實(shí)現(xiàn)工程量計(jì)算，Sum + Value + Substitute 組合實(shí)現(xiàn)數(shù)值帶單位求和，Substitute 嵌套一次替換多個(gè)字符。

一、Excel Substitute函數(shù)的語法

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

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

2、說明：

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

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

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

二、Excel Substitute函數(shù)的使用方法及實(shí)例

（一）替換所有相同字符的實(shí)例

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

圖1

2、公式說明：

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

（二）僅替換指定的相同字符實(shí)例

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

圖2

2、公式說明：

公式 =SUBSTITUTE(D2,"kg","",2) 與上例的公式相比只多了第四個(gè)參數(shù) 2，意思變?yōu)椋河每瘴谋?"" 替換 D2 中的第 2 個(gè) kg，從返回結(jié)果 kg2685 可知，只有第 2 個(gè) kg 被替換，第 1 個(gè)并未被替換；D3 和 D5、D2 一樣，其它的單元格只有一個(gè) kg，都沒有被替換。

（三）? 和 * 不是通配符的實(shí)例

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

圖3

2、公式說明：

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

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

（四）區(qū)分大小寫的替換實(shí)例

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

圖4

2、從替換結(jié)果可以看出，以大寫 W 開頭的 Word 并未被替換，而兩個(gè)小寫的 word 都被替換了，說明Substitute函數(shù)區(qū)分大小寫。

（五）僅替換單個(gè)數(shù)字實(shí)例

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

圖5

2、公式說明：

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

三、Excel Substitute函數(shù)的應(yīng)用實(shí)例

（一）Substitute函數(shù)的嵌套使用實(shí)現(xiàn)一次替換多個(gè)不相同字符

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

圖6

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

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

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

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

（二）Sum + Value + Substitute 組合實(shí)現(xiàn)數(shù)值帶單位的求和

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

圖7

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

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

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

C、則 VALUE(SUBSTITUTE(D2:D7,"kg","")) 變?yōu)?VALUE({"2685";"3500";"1258";"2050";"1050";"2200"})，進(jìn)一步計(jì)算，用 Value 把數(shù)組中的每個(gè)元素轉(zhuǎn)為數(shù)值；-- 與 Value 的作用一樣。

D、則公式變?yōu)?=SUM(2685;3500;1258;2050;1050;2200)，最后用 Sum 對(duì)數(shù)組中的元素求和。

（三）Substitute + Substitute + IsText 組合實(shí)現(xiàn)工程量計(jì)算

（1）用定義名稱計(jì)算

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

圖8

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

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

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

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

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

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

E、進(jìn)一步計(jì)算，公式變?yōu)?=IFERROR(7.68,"")，由于 7.68 不是錯(cuò)誤值，所以IfError函數(shù)返回 7.68；IfError函數(shù)用于錯(cuò)誤判斷，如果Evaluate 返回錯(cuò)誤，IfError 將返回 ""，否則返回 Evaluate 的返回值。

（2）用宏（VBA）計(jì)算

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

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

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

圖9

2、VBA 代碼說明：

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

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

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

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

四、Replace函數(shù)與Substitute函數(shù)的區(qū)別

Replace函數(shù)是用指定字符替換一定數(shù)目的字符，Substitute函數(shù)是用一個(gè)（或一串）字符替換另一個(gè)（或一串）字符；Replace函數(shù)主要用于一次替換一長(zhǎng)串字符，Substitute函數(shù)主要用于用一個(gè)詞替換另一個(gè)詞。假如要把一個(gè)詞替換另一個(gè)詞或把一長(zhǎng)串字符用空文本（""）替換，用兩個(gè)函數(shù)實(shí)現(xiàn)方法如下：

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

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

圖10

3、公式說明：

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

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

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

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

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