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

亮术网 2019-10-06 本网原创

在 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所示:

Excel Substitute函数的使用方法及实例

图1

2、公式说明:

公式 =SUBSTITUTE(D2,"kg","") 中,D2 为要替换部分字符的文本,kg 为被替换文本,"" 为替换文本,公式省略了最后一个参数“被替换文本序号”,默认替换所有的 kg;公式的意是:把 D2 中所有的 kg 用空文本 "" 替换掉。

 

(二)仅替换指定的相同字符实例

1、假如只替换 D 列数字中的第 2 个 kg。双击 D2 单元格,把公式 =SUBSTITUTE(D2,"kg","",2) 复制到 D2,按回车,返回仅替换掉第2 个 kg 的 kg2685;操作过程步骤,如图2所示:

Excel Substitute函数仅替换指定的相同字符实例

图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所示:

Excel Substitute函数中 ? 和 * 不是通配符的实例<

图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所示:

Excel Substitute函数区分大小写的替换实例

图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所示:

Excel Substitute函数仅替换单个数字实例

图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所示:

Excel Substitute函数的嵌套使用实现一次替换多个不相同字符

图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所示:

Excel Sum + Value + Substitute 组合实现数值带单位的求和

图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所示:

Excel Substitute + Substitute + IsText 组合实现工程量计算

图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所示:

Excel Evaluate + Substitute + Substitute + IsText 组合实现工程量计算

图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所示:

Excel Replace函数与Substitute函数的区别

图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函数简单,但要替换字符特别长时,很不方便写公式。