SUBSTITUTE函数有妙用（substitute函数怎么用）

SUBSTITUTE函数用于对指定字符串替换。

其语法结构为：SUBSTITUTE(需替换文本,旧文本,新文本,[替换第几个])。

1去掉空格

通常，由于信息录入的不规范，手机号码中常存在不规则的空格，可使用SUBSTITUTE函数去掉这些空格，输入公式：=SUBSTITUTE(B2," ","")，之后向下填充即可。

公式解释：公式的第2个参数为双引号内加空格，第3个参数的双引号代表空文本。

2隐藏数字

工作中，我们常常需对手机号码做掩码设置，可使用SUBSTITUTE函数将手机号码中间的4位数字，替换成*符号，之后输入公式，并向下填充：

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

公式解释：先使用MID函数，提取出指定位置的4个数字，之后再使用SUBSTITUTET函数将其替换为*符号。

3替换数字

使用SUBSTITUTE函数对指定数字替换，如下，将房号中的8栋统一替换为9栋，输入公式：=SUBSTITUTE(A2,"8","9",1)，之后向下填充。

公式解释：此处只需替换第一个数字8，因此公式内加上第3个参数1，代表替换第1个数字为8，即可。

4统计人数

如下表，需统计每天报考人数，输入公式：

=LEN(B2)-LEN(SUBSTITUTE(B2,"，",""))+1，并向下填充。

公式解释：先使用SUBSTITUTE函数将报考人员之间的逗号，替换为空，再使用LEN函数计算删掉逗号后的字符串长度。之后再计算包含逗号的字符串总长度，减去删除逗号的字符串长度，再加上1，即是报考人数。

5拆分文本

如下表，对报考人员拆分，输入公式：

=TRIM(MID(SUBSTITUTE($B2,"，",REPT(" ",100)),COLUMN(A1)*100-99,100))，并分别向右向下填充。

公式解释：

• 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函数，将多余空格删掉即可。