Excel SUBSTITUTE函数的5个经典应用

发布时间:2020-01-06 来源:网络


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


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


1、隐藏手机号码


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

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



2、替换指定的数字


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



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


3、统计人数


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

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



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


4、文本拆分


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

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


5、带单位数据求和


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

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



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