SUBSTITUTE函数的5个经典应用

SUBSTITUTE函数主要用来对指定字符串进行替换，今天技巧妹和大家分享它的5个经典应用。

SUBSTITUTE函数基本语法结构为：SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])

1、隐藏手机号码

实际生活中，我们为了保护个人隐私，需要对手机号码进行隐藏，这里可以利用SUBSTITUTE函数把手机号码中间的4位数字替换为*符号，输入公式并向下填充：

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

GIF

2、替换指定的数字

利用SUBSTITUTE函数我们可以对指定数字进行替换，如下表，我们想把房号中的8栋统一替换成9栋，输入公式从向下填充：=SUBSTITUTE(A2,'8','9',1)

GIF

公式说明：这里只需要替换第一个数字8，所以公式中加上第3个参数1，表示替换第1个数字8即可。

3、统计人数

如下表，我们要统计每天报考人数，输入公式并向下填充：

=LEN(B2)-LEN(SUBSTITUTE(B2,'，',''))+1

GIF

公式说明：先用SUBSTITUTE函数将报考人员之间的逗号替换为空，注意要替换的这个逗号是中文状态下的，再用LEN函数计算删掉逗号之后的字符串长度。接着计算出包括逗号的字符串总长度，减去删除逗号的字符串长度再加上1，即为报考人数。

4、文本拆分

如下表，如果我们想把每个报考人员进行拆分，输入公式并向右向下填充：

=TRIM(MID(SUBSTITUTE($B2,'，',REPT(' ',100)),COLUMN(A1)*100-99,100))

GIF

公式说明：

REPT(' ',100) ：先用REPT函数，将空格重复100次，得到100个空格；

SUBSTITUTE($B2,'，',REPT(' ',100))：用SUBSTITUTE函数将姓名中的逗号替换为100个空格；

MID(SUBSTITUTE($B2,'、',REPT(' ',100)),COLUMN(A1)*100-99,100) ：再使用MID函数依次从这个带有空格的新字符串中的第1、第101、第201位……开始截取长度为100的字符；

这样得到的字符串是带有多余空格的，最后使用TRIM函数将多余空格删除掉即可。

5、带单位数据求和

如下表，在表格里录入数据时带有单位，如何进行求和统计？输入公式：

=SUMPRODUCT(SUBSTITUTE(C2:C11,'元','')*1)&'元'

GIF

公式说明：SUBSTITUTE(D2:D10,'元','')是将C列中的“元”全部替换为空值，乘以1将文本转换为数值，再利用SUMPRODUCT函数求和。最后用连接符&加上“元”，使结果带上单位。