我是【桃大喵学习记】，欢迎大家关注哟~，每天为你分享职场办公软件使用技巧干货！

昨天，跟大家分享了Excel替换函数REPLACE的终极攻略，今天要跟大家分享的是Excel替换函数教程二：SUBSTITUTE函数的7种神奇用法！熟悉使用这些用法，不仅能让你工作效率翻倍，还能让你在职场中更加受到青睐！

SUBSTITUTE函数介绍

功能：主要是用于将指定数据中指定字符串的值替换为新值。

语法：SUBSTITUTE(字符串,原字符串,新字符串,[替换序号])

第1参数：字符串为需要替换其中字符的文本，或对含有文本的单元格的引用。

第2参数：原字符串为需要替换的旧文本。

第3参数：新字符串用于替换原字符串的文本。

第4参数：[替换序号] 为一数值，用来指定以“新字符串”替换第几次出现的“原字符串”。

如果指定了[替换序号] ，则只有满足要求的原字符串被替换；否则将用新字符串替换字符串中出现的所有原字符串。

用法一、文本替换

如下图所示，我们需要将A列单元格中的“赵飞”替换成“赵云”。

只需在目标单元格中输入公式：

=SUBSTITUTE(A1,"赵飞","赵云")

然后点击回车，下拉填充即可

解读：

公式中A1就是需要替换字符的单元格引用；"赵飞"是需要被替换掉的字符串；"赵云"是新字符串；所以结果就是将"赵飞"替换成了"赵云"。

用法二、去掉字符的空格

如下图所示将A列单元格姓名中的空格去掉，只需在目标单元格中输入公式：

=SUBSTITUTE(A2," ","")

然后点击回车，下拉填充即可

用法三、隐藏手机号中间4位以"*"号显示

有时为了保护个人手机信息不被泄露，常常需要把手机号码中间部分隐藏，这时我们就可以使用SUBSTITUTE函数隐藏手机号中间的4位，达到加密的目的。

只需在目标单元格中输入公式：

=SUBSTITUTE(D2,MID(D2,4,4),"****")

然后点击回车，下拉填充即可

解读：

①组合公式先通过MID(D2,4,4)获取手机号从左侧第四位开始，然后取四位数，最后通过SUBSTITUTE函数把取得的数值都替换"****"。

②那么同样的道理，也可以通过此方法隐藏身份证号码中的出生年月或者其它制定信息，但要注意的是此操作是不可逆的。

用法四、查找特定内容将第一次出现的删除，其它保留

如下图所示，把表格中订单编号中第一“-”横杠去掉，其它保留。

只需在目标单元格中输入公式：

=SUBSTITUTE(C2,"-",,1)

然后点击回车，下拉填充即可

解读：

查找特定内容将第一次出现的删除（替换成空），其它保留，主要是启用了第4参数，第4参数序号是多少，就替换多少个。

用法五、将“性别”中的“男”替换为2，“女”替换为1

如下图所示，我们将表格中的性别这一列中的“男”替换为2，“女”替换为1。

只需在目标单元格中输入公式：

=IF(B2="女",SUBSTITUTE(B2,"女",1),2)

用法六、统计文本数量

统计文本数量的情况我们经常用在统计人数这块，如下图所示每个部门参会人员使用"、"隔开，我们想统计每个部门有多少人，我们可以使用Len函数搭配SUBSTITUTE函数实现。

只需在目标单元格中输入公式：

=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1

然后点击回车，下拉填充即可

解读：

①公式中首先用Len函数计算源字符串的长度，Len(B2)代表计算B2的总长度。

②LEN(SUBSTITUTE(B2,"、",""))代表把人员清单里面的"、"全部替换为空，并计算长度。

③最后+1修正得到想要的结果，如果不理解+1的原因，可以查阅一下植树原理。

用法七、对带单位的数值进行求和

在日常工作中，我们会遇到需要对带有单位的数值进行求和，这时如果直接使用求和公式的话就无法处理，我们就可以通过SUBSTITUTE函数在进行求和。

在求和单元格中输入公式

=SUMPRODUCT(--SUBSTITUTE(D2:D8,"件",""))

然后点击回车，即可获取数据。

解读：

首先使用SUBSTITUTE(D2:D8,"件","")公式把数值中的单位替换成空，然后再使用双减号“--”减负运算，将文本数字转换为数值。最后在用SUMPRODUCT函数对其求和即可。

以上就是【桃大喵学习记】今天的干货分享~觉得内容对你有所帮助，别忘了动动手指点个赞哦~。大家有什么问题欢迎关注留言，期待与你的每一次互动，让我们共同成长！