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)),"")