Substitute函數用於在Excel中將一個字符或一個字符串替換為另一個字符或一個字符串。如果要替換的字符有多個相同的字符，則不僅可以替換所有字符，還可以替換指定的前幾個字符。替換函數也用於替換，這與本文末尾的替換函數不同。替代函數可以與IsText、Sum、Value、Evaluate等結合使用，也可以嵌套。例如，Evaluate+Substitute+Substitute+IsText組合實現數量計算，Sum+Value+Substitute組合可以添加單位值，替換嵌套替換多個字符。
一、 Excel替代函數的語法
1、表達式：替換（文本、舊文本、新文本，[實例數]）
2、說明：
A、 Substitute函數用於用一個或一個新字符串替換指定的舊字符。如果要替換的字符相同，則可以使用參數Instance_Num指定要替換的字符；如果Instance_Num為1，則替換第一個字符和另一個字符，依此類推。如果省略Instance_Num，則替換所有相同的字符。
B、 替換函數不支持通配符問號（？）星號（*）例如，*in Old_Text或New_Text不代表任何一個或多個字符，只代表*本身。
C、 替代函數區分大小寫。例如，文本中有“ab”和“ab”，如果舊文本中只寫“ab”，則“ab”不被替換。
二、 Excel替代函數實例
（一） 替換所有相同的字符
1、如果你想替換D列中的所有“lb”。雙擊單元格D2，將公式=SUBSTITUTE（D2，“lb”，“”）複製到D2，按回車鍵，返回1850；選擇D2，將鼠標移到D2右下角的單元格填充手柄上，鼠標變為黑體加號後，雙擊左鍵，D列剩餘數字中的“lb”也被替換；操作步驟如圖1所示：
圖1
2、公式說明：
D2是文本，“lb”是舊的“lb”，“是新的”文本，省略最後一個參數實例“Num”，默認替換公式中的所有“lb”=替換（D2，“lb”，“”）；公式的意思是：用空文本“”替換D2中的所有“lb”。
（二） 僅替換指定的相同字符
1、如果只更換D列中的第二個“lb”。雙擊單元格D2，將公式=SUBSTITUTE（D2，“lb”，“”，2）複製到D2，按回車鍵，返回“lb1850”，只替換第二個“lb”；操作步驟如圖2所示：
圖2
2、公式說明：
公式=SUBSTITUTE（D2，“lb”，“”，2）與上例的公式相比，只有第四個參數2，意思是：用空文本替換D2中的第二個“lb”，從返回的結果“lb1850”可以看出，只替換了第二個“lb”，第一個不替換；D3與D5和D2相同，其他單元格是隻有一個“磅”，不更換。
（三） 是嗎？和*不是通配符
1、雙擊單元格B1，將公式=SUBSTITUTE（A1，“d*”，“”複製到B1，按Enter鍵，返回到數字78969759389231
哪個“d*”被替換；雙擊B2，複製公式=SUBSTITUTE（A2，“？”，“0”）到B2，按回車鍵，返回25802765093802378哪個“？”替換為0；操作步驟如圖3所示：
圖3
2、公式說明：
A、 “d*”中的*只在公式=SUBSTITUTE（A1，“d*”，“”中表示自己，如果它表示任何字符，則替換“d”之後的所有字符，表示函數SUBSTITUTE不支持通配符*。
B、 公式中的替換字符=替換字符（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”，同時替換兩個小寫單詞，這表示替換函數區分大小寫。
（五） 僅替換單個數值實例
1、如果要替換“20,2,23,16,36,2,28,52”中的單個2，則不能替換2，例如23。雙擊單元格B1，將公式=SUBSTITUTE（A1，“，2，“，”，“，”）複製到B1，按Enter鍵，返回結果“20,23,16,36,28,52”，替換2，步驟如圖5所示：
圖5
2、公式說明：
被替換的文本是“2”，在公式=SUBSTITUTE（A1，“，2，“，”，“，”）中，2前後有逗號，主要用於區分2，如23，如果只寫“2”，則A1的2都被替換。
三、 Excel替代函數的應用實例
（一） Excel替換多個字符實例：一次替換多個不同字符的替換函數的嵌套
1、如果要替換A列中的左右雙引號和空格。雙擊單元格D2，將公式=SUBSTITUTE（SUBSTITUTE（A2，“，”），“”，“）複製到D2，按Enter鍵，A2中的雙引號和空格將被替換；選擇D2，使用雙擊單元格填充句並替換其餘單元格的雙引號；操作步驟如圖6所示：
圖6
2、公式=替換（替換（A2，“，”），“，”，“”描述：
A、 內層替換（A2，“，”）用於替換空格，它返回結果“Apple”。
B、 公式變為=SUBSTITUTE（“”Apple“”、“”和“”），最後用空格替換雙引號（“），替換A2左右兩側的雙引號。
（二） Sum+Value+Substitute組合以添加帶單位的值
1、有一張水果銷售表，售後單位是“磅”，現在需要彙總一下。雙擊單元格D8，將公式=SUM（VALUE（SUBSTITUTE（D2:D7，“lb”，“”））複製到D8，按Ctrl+Shift+Enter返回求和結果23968；雙擊D8，將VALUE改為--，按Enter，返回相同結果；處理步驟如圖7所示：
圖7
2、公式=和（值（替換（D2:D7，“lb”，“”））說明：
A、 公式是數組公式，因此按Ctrl+Shift+Enter；D2:D7將D2到D7中的所有值作為數組返回。
B、 然後SUBSTITUTE（D2:D7，“lb”，“”）變為SUBSTITUTE（{“1850lb”；“2890lb”；“3450lb”；“3580lb”；“5698lb”；“6500lb”}，“lb”，“”），然後從數組中獲取第一個元素“1850lb”，然後用空文本替換“lb”，依此類推，最後返回{“1850”；“2890”；“3450”；“3580”；“5698”；“6500”}。
C、 然後VALUE（SUBSTITUTE（D2:D7，“lb”，“”）變為VALUE（{“1850”；“2890”；“3450”；“3580”；“5698”；“6500”}），進一步計算，使用VALUE將數組中的每個元素轉換為一個值；--與VALUE一樣。
D、 公式變為=SUM（1850；2890；3450；3580；5698；6500），最後將元素添加到數組中。
（三） Substitute+Substitute+IsText組合計算數量
（1） 按定義名稱計算
1、如果你想計算牆的體積。選擇計算公式所在的單元格A2，選擇“公式”頁籤，單擊“定義名稱”，打開“新名稱”窗口，在“名稱”後輸入“qua”，在“範圍”中選擇“Sheet1”，複製公式=IFERROR（EVALUATE（SUBSTITUTE（Sheet1）！$A$2，“[”，“*ISTEXT（“[”，“]，“]”）”），在“引用”右邊的輸入框中，點擊“確定”，名稱定義完成；選擇B2，輸入=qua，按回車，返回計算結果7.68；操作過程步驟如圖8所示：
圖8
2、公式=IFERROR（評估（替代）（替代（表1！）！$A$2，“[”，“*ISTEXT（“[”，“]”，“]”）”），解釋：
A、 第1張！$A$2是對錶1中A2單元格的絕對引用。定義名稱時，單元格需要絕對引用，否則將找不到單元格。
B、 最內層的配方替代品（表1！$A$2，“[”，“*is text（“[”）用於將所有左括號[在A2中]替換為*ISTEXT（“[；”，其目的是使用ISTEXT函數來確定A2中數字後面的註釋是否為文本，如果是，則返回True（即1），否則返回False（即0）；例如，ISTEXT（“[牆高]”）返回True。
提示：替換雙引號時，請使用兩個雙引號，例如，將[替換為*ISTEXT（“[”），因為您需要在[之前添加一個雙引號，所以在*ISTEXT（“[”）中添加兩個雙引號。
C、 然後替換（替換（表1！$A$2，“[”，“*ISTEXT（“[”，“]，“]”）”）替換為（“（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返回錯誤，If error將返回“”，否則返回Evaluate的返回值。
（2） 用宏計算（VBA，Excel VBA替換字符串）
1、並以牆體體積計算為例。在Excel窗口中，按Alt+F11打開VBA編輯窗口，單擊“插入”，在彈出菜單中選擇“模塊”，創建新模塊，然後複製以下代碼：公共子測試（）[B3]=求值（Application.Substitute（Application.Substitute（[B2]，“[”，“*ISTEXT（“[”，“]，“]））端接頭進入模塊後，點擊“運行”，在彈出的菜單中選擇“運行子表/用戶表”，計算完成並將結果7.68輸出到B3單元格，再次按Alt+F11切換到Excel窗口查看，操作過程步驟如圖9所示：
圖9
2、VBA代碼說明：
A、 [B2]表示引用單元格B2。若要引用VBA中的單元格，請將其括在方括號[]中。
B、 在VBA中使用函數時，需要在函數前面添加“Application”，否則會提示您查找不到該函數，如Application.Substitute。
C、 [B3]用於輸出Evaluate的執行結果，因此在執行代碼後，B3中有7.68。
提示：以上兩種數量計算方法在保存時，“另存為類型”需要選擇“Excel宏啟用工作簿”，否則下次將無法正確執行。
四、 替換函數與替換函數的區別
替換函數用指定的字符替換一定數量的字符，替換函數用一個（或一個字符串）字符替換另一個（或一個字符串）；替換函數主要用於一次替換一長串字符，代詞功能主要用於將一個詞替換成另一個詞。如果要將一個單詞替換為另一個單詞或將長字符串替換為空文本（“”），請使用兩個函數實現以下操作：
1、如果你想用A1中的“Word”替換“Excel”。雙擊單元格B1，將formula=REPLACE（A1,1,4，“Excel”）複製到B1，按Enter鍵，返回“Excel table technology”；雙擊B2，將formula=REPLACE（A1，“Word”，“Excel”）複製到B2，按Enter鍵，返回“Excel table technology”。
2、如果要用空文本替換A4中的一長串凌亂字符。雙擊B4，將公式=REPLACE（A4，FIND（“8”，A4），FIND（“4 fun”，A4）-FIND（“8”，A4）+2，”）複製到B4，按Enter，返回“Excel函數教程”；然後雙擊B5，將公式=REPLACE（A4，“8ut43it-r*753iot9oytTrey8345fdh4”，”）複製到B5，按Enter，也返回“Excel函數教程”；操作過程步驟如圖所示10個：
圖10
3、公式說明：
A、 公式=REPLACE（A1,1,4，“Excel”）是指從A1中的第一個字符替換4個字符為“Excel”，即替換A1中的“Word”，“Word”正好是4個字符。公式=SUBSTITUTE（A1，“Word”，“Excel”）將A1中的“Word”直接替換為“Excel”；從這兩個公式可以看出，後者更簡單。
B、 公式=REPLACE（A4，FIND（“8”，A4），FIND（“4 fun”，A4）-FIND（“8”，A4）+2，”）表示，將A4中第7個字符中的34個字符替換為空文本（“”），這恰好是A4中的混亂字符和最後一個空格。
FIND（“8”，A4）返回要在A4中替換的字符串的第一個字符（即8）的位置，結果是7。FIND（“4 fun”，A4）-FIND（“8”，A4）+2用於計算要替換的字符數，即使用要替換的字符的最後一個位置減去要替換的字符的第一個字符的位置並加上2，add 2也用於替換被替換的字符串後的空格。FIND（“4fun”，A4）用於返回要在A4中替換的字符的最後一個字母的位置，結果是39，原因是要找到“4fun”，因為A4中有多個4以避免出錯。
C、 公式=SUBSTITUTE（A4，“8ut43it-r*753iot9oyt5trey8345fdh4”，“”）直接將“8ut43it-r*753iot9oyt5trey8345fdh4”替換為“”，該公式比使用Replace函數簡單，但在編寫公式時替換特別長的字符非常不方便。
關鍵字: Excel SUBSTITUTE lb
