substitute函数怎么用

2月前发布

梅英疏淡,冰澌溶泄,东风暗换年华。 –《望海潮·洛阳怀古》宋.秦观

在Excel中,说到换不知道大家首先想到的是什么函数呢?

如果对函数了解比较多的话,可能会想到REPLACE函数以及Substitute函数。

这两个函数的区别在于,REPLACE函数侧重于位置点,Substitute函数则侧重于内容的替换。

这两个函数的具体的用法区别我们后面再讲,今天主要和大家分享的是SUBSTITUTE函数的用法

首先,我们先看下SUBSTITUTE函数的基本用途含义。

SUBSTITUTE函数的用途是对指定的字符进行替换。

函数定义:SUBSTITUTE(text,old_text,new_text,[instance_num])text :字符串,可以是文本内容也可以是对含有文本的单元格的引用。old_text :要被替换掉的字符new_text : 替换后的字符内容Instance_num :可以省略,代表替换text中的第几个old_text,

知道了它的基本语法,下面就来给大家看下实际的应用吧。

案例一、名称替换

我们把A1单元格内容的"彩虹"替换为"学习",我们可以输入公式:

=SUBSTITUTE(A1,"彩虹","学习")

案例二、去除字符间空格

把A列名单中的中间的空格去除,我们可以输入公式:

=SUBSTITUTE(A2," ","")

案例三、手机号中间4位以"*"号显示

有时为了保护我们的个人手机信息,常常需要把手机号码中间部分隐藏,这时我们就可以使用Substitute函数了

在C2输入公式:

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

MID函数取手机号码的中间4位,再使用Substitute函数替换掉

案例四、搭配Vlookup函数查找

我们在使用Vlookup函数进行查找的时候,可能会遇到明明看起来一样,可是就是查找不到,这时就要考虑是否存在空格了,我们可以搭配substitute函数来使用了

=VLOOKUP(SUBSTITUTE(F2," ",""),B1:D15,3,0)

案例五、统计部门人数

现在每个部门有一些成员,每个成员间使用"、"隔开,我们想统计每个部门有多少人,我们可以使用Len函数搭配Substitute函数实现

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

Len(B2)代表计算B2的总长度

SUBSTITUTE(B2,"、",""))代表把人员清单里面的"、"全部替换为空;

再使用Len函数统计替换后的字符长度,两个一减即统计"、"的个数,最后再加1,即为人员数了

案例六、带单位文本求和

以下每个员工的销售金额都带有后缀"元",如果我们想计算所有员工的销售总额,直接使用SUM函数是不可以的,这时我们可以使用SUbstitute函数替换掉字符,再进行计算

=SUMPRODUCT(SUBSTITUTE(B2:B10,"元","")*1)

注意:

1、因为SUBSTITUTE(B2:B10,"元","")是数组计算,所以我们不能使用SUM函数了,故采用的是SUMPRODUCT函数,如果使用SUM函数,必须按Ctrl+Shift+Enter结束

2、因为SUBSTITUTE(B2:B10,"元","")替换后的金额是文本型,所以还需要把其转换为数值型,故对其进行*1计算

案例七、数据分列

我们有如下的数据,其中项目栏位是把星期数以及课程名称写一起的,中间使用"-"隔开,我们想对其进行分列,此时也可以使用Substitute函数

=TRIM(MID(SUBSTITUTE($A2,"-",REPT(" ",99)),COLUMN(A1)*100-99,100))

公式解释:

SUBSTITUTE($A2,"-",REPT(" ",99)):代表将A2单元格的"-"替换为99个空格

COLUMN(A1)*100-99:当向右拖动的时候,即分别生成1,101,201的数

再使用MID函数提取从1,101,201…等开始取数,取100位。

为什么是取100位呢?

因为A列的字符串至少包含一个字符,当再加上99个空格时,所有位数至少是100

最后多余的空格使用TRIM函数去除即可

案例八、拆分同类项

我们需要把A、B的横向的数据拆分为D、E列竖向的数据,我们同样可以借用Substitute函数来实现

=IFERROR(TRIM(MID(SUBSTITUTE(LOOKUP(,0/(A$2:A$6=D2),B$2:B$6),"、",REPT(" ",99)),100*COUNTIF(D$2:D2,D2)-99,100)),"")

以上就是给大家分享的Substitute函数的8种用法实例,如果有什么问题,欢迎大家一起讨论。

如果觉得文章对你有帮助的话,希望大家帮忙点赞加分享哦~,谢谢

本文由彩虹Excel原创,欢迎关注,带你一起长知识!

举报/反馈
© 版权声明
THE END
喜欢就支持以下吧
点赞0 分享
评论 抢沙发

请登录后发表评论