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函数简单，但在编写公式时替换特别长的字符非常不方便。
