在Excel中,经常会用到两个“替换函数”,一个是SUBSTITTE函数,另外一个是REPLACE函数。这两个函数各自有各自的长处,是十分常用的一对替换函数。今天世杰老师给大家讲一下这两个函数的最常用的案例。
1、函数介绍
SUBSTITUTE函数是用于将目标文本字符串中的指定的字符替换为新的字符串,函数语法如下:
SUBSTITUTE(文本字符串,旧文本字符串,新文本字符串,[第几次出现])
该函数区分大小写进行替换,最后一个参数是可选参数,当省略时将所有的旧文本字符串替换成新的文本字符串。
REPLACE函数是用于指点指定长度的字符串替换为不同的字符串。具体的语法如下:
REPLACE(旧文本字符串,开始位置,替换的长度,新的文本字符串)
该函数主要是按照位置进行替换。
2、统计参赛的人数
如图所示,下面的表中每个组都有若干个人参加比赛,统计每个组的人数。
在C2单元格中输入以下公式,向下填充至C5单元格。
=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1
![f5e7f54400627e987781c01edc6fee5a.png](https://i-blog.csdnimg.cn/blog_migrate/940728130449744d3219bfeef9a50f24.jpeg)
公式解释:
LEN(B2)是用来测量B2单元格中的文本的长度;
LEN(SUBSTITUTE(B2,",",""))是将B2单元格中的文本字符串中的中的逗号替换成空白,然后使用LEN测量出没有逗号的文本字符串的长度。然后使用原文本的长度减去替换成逗号的长度,可以得到逗号的数量,再加上1就是成员的长度。
3、隐藏电话号码
如图所示,在一期抽奖中将获奖者名单中的电话号码。为了保护个人隐私,需要将电话号码中的第4-7位内容隐藏。
在C2单元格中输入以下公式,然后向下填充至最后一个单元格。
=REPLACE(B2,4,4,"****")
![62bfe9fc4e6a8006c47de4085223a912.png](https://i-blog.csdnimg.cn/blog_migrate/4a32b836ef8e8020af87a477b5c4d5bc.jpeg)
公式解释:
公式的作用是从文本字符串的第4个字符的位置开始,用“****”进行替换。
最后隐藏B列,即可实现隐藏电话号码中的4位。
当然也可以使用SUBSTITUTE函数来完成上面的例子。
在C2单元格中输入以下公式,向下填充至最后一个单元格。
=SUBSTITUTE(B3,MID(B3,4,4),"****")
![31d1dae98abd8d3afc4799a2a0ee4b76.png](https://i-blog.csdnimg.cn/blog_migrate/b96583b5f989d70e710bc2cb6d56e676.jpeg)
公式解释:
使用MID函数将电话号码中的4位提取出来,然后使用SUBSTITUTE函数替换成指定的字符串。
4、替换指位位置
将下表中的文本中的第2个“梦想”两个字替换成“梦想起点”。
在B2单元格中输入公式,按Enter键完成。如下图所示:
=SUBSTITUTE(A2,"金融","财务")
![113c65cef7f38ae3a446cd51eb983d48.png](https://i-blog.csdnimg.cn/blog_migrate/d6f3a93a7e66f94db0f71f3a91db0dfd.jpeg)
注:该函数的最后一个参数可以指定具体替换的位置,是可选参数。
5、文本求和
如下图所示:,下表中是某次活动的一个购买清单,每个金额后面跟了一个元,要求求和。在D5单元格中输入公式,,按Enter键完成。
=SUMPRODUCT(--SUBSTITUTE(D2:D4,"元",""))
![f733bd6c33698bd36e17d9bc75608891.png](https://i-blog.csdnimg.cn/blog_migrate/c11e5140fbb4bfee2abe721fd5f094f7.jpeg)
注:“--”是减负的意思,强制地把文本型的数字转换成数值型的。然后使用SUMPRODUCT来求和
每一个人的小有成就,源于对自己的不断要求和努力,希望和你一起加油。
关注小编,分享更多干货。