在EXCEL表格里，使用CTRL+H可以打开查找替换窗口，替换掉表格里的部分内容，而在函数的世界里，同样也有函数能替换表格里的内容，甚至更加灵活，这个函数就是SUBSTITUTE。这个函数比较长，记不下来也没有关系，只要记住前面的三个字母，EXCEL会自动匹配出函数。

SUBSTITUTE函数，将字符串中部分字符串以新字符串替换。

语法结构：

【资料图】

=SUBSTITUTE(Text,Old_text,New_text,Instance_num)

=SUBSTITUTE(要替换的文本，旧文本，新文本，替换第几个)

四个参数，前三个必填，最后一个选填，意思是如果要替换的旧文本有多个，可以指定替换第几个，如果省略，则全部替换。

案例说明：

全部替换

如图所示，原来的产品编号都是A开头的，现在需要将原来的A开头的编号修改为B开头。

输入函数公式：=SUBSTITUTE(A2,"A","B")

公式解读：将A2单元格内字母A替换成字母B，有多少替换多少。

部分替换

表格里原来的产品编号有多个字母A,现在仅需要将第二个A替换成B，函数公式：=SUBSTITUTE(A2,"A","B",2)

公式解读：在上面函数的基础上，输入第四个参数“2”，即指定替换A2单元格内第二个A。

隐藏手机号码中间数字

很多时候，手机号码、身份证号码、工资信息因个人隐私问题需要隐藏部分数据，除了前面学习的REPLACE函数外，可以使用SUBSTITUTE函数来完成。

函数公式：=SUBSTITUTE(A22,MID(A22,4,4),"****")

公式解读：这里使用MID函数提取出A22单元格内电话号码从第4位起的连续4位数字。接着用SUBSTITUTE函数将MID函数提取出来的4个数字替换成四个星号。

统计文本串里的人数

如图所示，在每一个单元格内输入了多个加班人员的姓名，中间用斜杠“/”隔开人名，需要统计出每一个单元格内总人数。

函数公式“=LEN(B31)-LEN(SUBSTITUTE(B31,"/",""))+1”

公式解读：这里使用常规的计算显示无法算出人的个数。

好在每一个人名的中间都有“/”符号，即可计算斜杠的个数，再加1不就是人名的个数。

使用LEN函数计算出带斜杠的字符个数，再使用SUBSTITUTE函数将每一个单元格内“/”去除后计算字符个数，两个统计结果相减后再加1，不就是人的个数了么？

带单位数字就和

部分表格的数据后面添加了单位，又需要对数字汇总，大家都知道，这里的单位直接添加到了数字的后面，让数字变成了文本，是不可以直接使用SUM函数求和的。

函数公式：=SUM(--SUBSTITUTE(C37:C46,"元",""))&"元"

=SUM(--SUBSTITUTE(C37:C46,"元","")*1)&"元"

公式解读：带单位的数字是文本，我们就要把文本变成纯数值才能求和，于是用SUBSTITUTE函数将上方数字的单位全部去除，也就是将函数的第三个参数设置为空白，然后汇总求和，注意的是，使用SUBSTITUTE函数删除了单位后，还是文本，需要在函数的后面“*1”或者在前面添加两个“-”，将文本数字转成数值后再使用SUM函数求和。

最后如果需要添加单位的话，在用连接符“&”连接上单位即可。

怎么样，关于SUBSTITUTE函数的替换功能是不是比替换窗口里的替换操作更灵活，更多样呢？

想了解更多精彩内容，快来关注和我学办公软件

标签：