Excel中的替换神技，SUBSTITUTE函数使用技巧详解

梅英疏淡，冰澌溶泄，东风暗换年华。

--《望海潮·洛阳怀古》宋.秦观

在Excel中，说到换不知道大家首先想到的是什么函数呢？

?如果对函数了解比较多的话，可能会想到REPLACE函数以及Substitute函数。

这两个函数的区别在于，REPLACE函数侧重于位置点，Substitute函数则侧重于内容的替换。

这两个函数的具体的用法区别我们后面再讲，今天主要和大家分享的是SUBSTITUTE函数的用法

首先，我们先看下SUBSTITUTE函数的基本用途含义。

SUBSTITUTE函数的用途是对指定的字符进行替换。

函数定义：

SUBSTITUTE(text,old_text,new_text,[instance_num])

text ：字符串，可以是文本内容也可以是对含有文本的单元格的引用。

old_text ：要被替换掉的字符

new_text : 替换后的字符内容

Instance_num ：可以省略，代表替换text中的第几个old_text,

知道了它的基本语法，下面就来给大家看下实际的应用吧。

案例一、名称替换

我们把A1单元格内容的"彩虹"替换为"学习"，我们可以输入公式：

=SUBSTITUTE(A1,"彩虹","学习")

案例二、去除字符间空格

把A列名单中的中间的空格去除，我们可以输入公式：

=SUBSTITUTE(A2," ","")

案例三、手机号中间4位以"*"号显示

有时为了保护我们的个人手机信息，常常需要把手机号码中间部分隐藏，这时我们就可以使用Substitute函数了

在C2输入公式：

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

MID函数取手机号码的中间4位，再使用Substitute函数替换掉

案例四、搭配Vlookup函数查找

我们在使用Vlookup函数进行查找的时候，可能会遇到明明看起来一样，可是就是查找不到，这时就要考虑是否存在空格了，我们可以搭配substitute函数来使用了

=VLOOKUP(SUBSTITUTE(F2," ",""),B1:D15,3,0)

案例五、统计部门人数

现在每个部门有一些成员，每个成员间使用"、"隔开，我们想统计每个部门有多少人，我们可以使用Len函数搭配Substitute函数实现

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

Len(B2)代表计算B2的总长度

SUBSTITUTE(B2,"、",""))代表把人员清单里面的"、"全部替换为空；

再使用Len函数统计替换后的字符长度，两个一减即统计"、"的个数，最后再加1，即为人员数了

案例六、带单位文本求和

以下每个员工的销售金额都带有后缀"元"，如果我们想计算所有员工的销售总额，直接使用SUM函数是不可以的，这时我们可以使用SUbstitute函数替换掉字符，再进行计算

=SUMPRODUCT(SUBSTITUTE(B2:B10,"元","")*1)

注意：

1、因为SUBSTITUTE(B2:B10,"元","")是数组计算，所以我们不能使用SUM函数了，故采用的是SUMPRODUCT函数，如果使用SUM函数，必须按Ctrl+Shift+Enter结束

2、因为SUBSTITUTE(B2:B10,"元","")替换后的金额是文本型，所以还需要把其转换为数值型，故对其进行*1计算

案例七、数据分列

我们有如下的数据，其中项目栏位是把星期数以及课程名称写一起的，中间使用"-"隔开，我们想对其进行分列，此时也可以使用Substitute函数

=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",99)),COLUMN(A1)*100-99,100))

公式解释：

SUBSTITUTE($A2,"-",REPT(" ",99))：代表将A2单元格的"-"替换为99个空格

COLUMN(A1)*100-99：当向右拖动的时候，即分别生成1,101,201的数

再使用MID函数提取从1,101,201...等开始取数，取100位。

为什么是取100位呢？

因为A列的字符串至少包含一个字符，当再加上99个空格时，所有位数至少是100

最后多余的空格使用TRIM函数去除即可

案例八、拆分同类项

我们需要把A、B的横向的数据拆分为D、E列竖向的数据，我们同样可以借用Substitute函数来实现

=IFERROR(TRIM(MID(SUBSTITUTE(LOOKUP(,0/(A$2:A$6=D2),B$2:B$6),"、",REPT(" ",99)),100*COUNTIF(D$2:D2,D2)-99,100)),"")

以上就是给大家分享的Substitute函数的8种用法实例，如果有什么问题，欢迎大家一起讨论。

如果觉得文章对你有帮助的话，希望大家帮忙点赞加分享哦~，谢谢

本文由彩虹Excel原创，欢迎关注，带你一起长知识！