前面的文章里给大家介绍了几个特别强大的函数,如VLOOKUP,LOOKUP,SUMIFS,SUMPRODUCT等函数,今天给大家再介绍一个比较强大的函数——SUBSTITUTE函数。
●名称:SUBSTITUTE
●功能:将某一串文本中的值替换成其他值。
●套路:SUBSTITUTE(替换目标,目标里的那个字符,在换的新字符,[替换第几个])
01
替换单个字符
将下表中的文本中的“金融”两个字替换成“财务”。
在B2单元格中输入公式:
=SUBSTITUTE(A2,'金融','财务'),按Enter键完成。如下图所示:
02
替换指定位置的字符
将下表中的文本中的第2个“梦想”两个字替换成“梦想起点”。
在B2单元格中输入公式:
=SUBSTITUTE(A2,'金融','财务'),按Enter键完成。如下图所示:
注:该函数的最后一个参数可以指定具体替换的位置,是可选参数。
03
统计人数
下表中是某一次参会的人员情况,现统计人员参会情况。
在C2单元格中输入公式:
=LEN(B2)-LEN(SUBSTITUTE(B2,',',''))+1,按Enter键完成。如下图所示:
注:LEN函数是提取字符串的长度,上题中是先计算原字符串的长度,然后再将字符串的逗号替换成空白,计算替换后字符串的长度,最后两个减加1就是姓名的个数。
04
隐藏手机号码中的重要部分
下表中某次获奖的人员名单,要求对外公布时隐藏中间的5位。
在C2单元格中输入公式:
=SUBSTITUTE(B2,MID(B2,4,5),'*****'),按Enter键完成。如下图所示:
注:MID函数是截取手机号码中的中间5位,然后将其替换成5个星号即可。
05
文本求和
下表中是某次活动的一个购买清单,每个金额后面跟了一个元,要求求和。
在D5单元格中输入公式:
=SUMPRODUCT(--SUBSTITUTE(D2:D4,'元','')),按Enter键完成。如下图所示:
注:“--”是减负的意思,强制地把文本型的数字转换成数值型的。然后使用SUMPRODUCT来求和。
来源:24财务excel,作者:刘必麟
联系客服