替换函数SUBSTITUTE的5个应用技巧

作者：郅龙来源：部落窝教育发布时间：2021-10-08 10:10:15点击：2811

分享到:

收藏收藏人气：0人

版权说明：原创作品，禁止转载。

编按：

Hello大家好，今天给大家介绍一下SUBSTITUTE函数，它可以将数据中的旧值替换为新值，与我们熟悉的“查找替换”功能相似。很多小伙伴可能会说，简单的查找替换就能解决的问题，我为什么还要学习用复杂的函数？相比Excel的基础操作，函数可以构建和数据源之间的动态关联，当我们的数据源发生变化和，函数会自动更新结果，但基础操作并没有。所以我们今天就通过5个案例一起来学习一下SUBSTITUTE函数吧~

今天给大家介绍一个可以进行查找替换的函数---SUBSTITUTE函数，SUBSTITUTE函数的基础语法是：SUBSTITUTE (要替换的文本,旧文本,新文本,[替换第几个])。最后一个参数，[替换第几个]，是可以省略的，如果要替换的文本存在多个的话，省略这个参数表示替换全部。

先通过一个示例来掌握SUBSTITUTE函数的基本用法。

示例1：将单元格里的“付款”替换成“账期”

公式为=SUBSTITUTE(B2,"付款","账期")。

这个公式省略了最后一个参数，所以将单元格里的所有“付款”都换成“账期”。如果只想替换第一个“付款”，公式需要修改为：=SUBSTITUTE(B2,"付款","账期",1)。

如果原来是“预付款”不进行替换，公式可以修改为：=SUBSTITUTE(B2,"天付款","天账期",1)。

通过这个例子相信大家对于SUBSTITUTE函数的基本用法应该明白了。不过在实际应用中，单独使用SUBSTITUTE函数的机会很少，基本上都是和其他函数组合使用的，下面的几个例子都是组合套路，非常实用。

示例2：SUBSTITUTE组合MID加密手机号

这里所说的加密就是将手机号的中间四位显示成*，公式为：

=SUBSTITUTE(A2,MID(A2,4,4),"*****")。

公式的原理很简单，MID(A2,4,4)是从手机号的第4位开始提取4个数字，用SUBSTITUTE函数把这部分内容换成"*****"，从而实现了手机号加密。

示例3：SUMPRODUCT组合SUBSTITUTE实现带单位的数字求和

公式为：=SUMPRODUCT(--SUBSTITUTE(A2:A13,"元",""))。

首先用SUBSTITUTE(A2:A13,"元","")将区域中数据的单位“元”替换为空，因为SUBSTITUTE函数得到的结果是文本格式，所以前面用两个负号将替换后的数据变成数值，最后用SUMPRODUCT函数对这一组数字求和。（注意：数字加单位是一种不规范的用表习惯，如果确实需要加单位可以用自定义格式实现。）

示例4：LEN组合SUBSTITUTE统计一个单元格内的人数

公式为：=LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1

在这个公式中，LEN(B2)取得B2单元格中字符串的长度。LEN(SUBSTITUTE(B2,"、",))+1的意思是用LEN计算不含顿号的字符串长度。在这个例子中，人名之间的间隔符是顿号，最后加1，是因为最后一个人名没有顿号。

用B2原有的长度减去被替换掉人名之间间隔符的长度，也就是人数。使用这个公式要注意，每个名字之间的分隔符必须是一样的，否则统计结果就会出错。

示例5：五个函数联手实现数据分列

将示例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函数将多余空格删除掉。如果实在不好理解会套用即可。