Excel Substitute函数用法的8个实例，含工程量计算、嵌套一次替换多个字符、带单位求和与Replace的区别

在 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,"[","*ISTEXT(""["),"]","]"")")),"") 复制到“引用位置”右边的输入框中，单击“确定”，则名称定义完成；选中 B2，输入 =gcl，按回车，返回计算结果 7.68；操作过程步骤，如图8所示：

图8

2、公式 =IFERROR(EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A,"[","*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函数简单，但要替换字符特别长时，很不方便写公式。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。