五种SUBSTITUTE函数用法，总有一个奇奇怪怪~

HI，大家好，我是星光。

今天给大家分享一个函数，叫做SUBSITUTE。它是Excel文本处理中最常使用的函数之一，可以将字符串中的旧值替换为新值，类似于基础操作里的查找与替换功能，因此又被称为函数中的查找替换机。

1 丨

基本语法

SUBSTITUTE函数的基本语法如下：

SUBSTITUTE(源字符串,旧文本,新文本,[替换第几个旧文本])

其中第4个参数[替换第几个旧文本]是可以省略的，绝大部分情况下也都用不上。

举个简单的小例子。

如上图所示的数据，需要将B列的二班，替换为一班，C2单元格输入以下公式，向下复制填充即可。

• 
  

=SUBSTITUTE(B2,'二班','一班')

公式中B2是源字符串，'二班'是被替换的旧值，'一班'是替换成的新值——是不是很简单？

有朋友可能会说，这事我用查找替换就可以完成，为什么要用函数？

相比于基础操作，函数的优势在于它可以构建和数据源之间的动态关联，当数据源发生了变化，函数可以自动更新获取结果；另外，也可以作为一个子函数，将计算结果嵌套在其它函数中，完成远比基操复杂的计算目标。

换而言之，函数具有小规模自动化处理数据的能力，而基础操作自身并不具备这种能力。当基操具备这种能力时，它就——变成了PowerQuery

打个响指，给大家循序渐进举几个例子，分享下SUBSTITUTE函数的常用技巧和套路。

2 丨

自古深情留不住

只有套路得人心

1 ▎隐藏手机号中间5位

如下图所示的数据，需要将B列的手机号隐藏中间5位，结果如C列所示。

C2单元格输入公式如下：

=SUBSTITUTE(B2,MID(B2,4,5),'*****')

公式先用MID(B2,4,5)函数从B2单元格第4个字符开始取5个长度的结果，然后再使用SUBSTITUTE函数把这部分替换为5个星号。

2 ▎对含单位的数据进行求和

如下图所示的数据，需要在B7单元格对B2:B6区域的人数统计求和。由于区域内的人数并非数值，无法直接使用SUM函数。

B7单元格输入公式如下：

=SUMPRODUCT(

SUBSTITUTE(B2:B6,'人','')*1)

公式先运行：

SUBSTITUTE(B2:B6,'人','')

将区域内的单位'人'，替换为空，获取结果如下：

• 
  

{'3';'7';'8';'2';'6'}

文本函数返回的结果必然是文本，因此这些数值也都属于文本型数值，使用数学运算*1，强制转换为数值，最后再使用SUMPRODUCT函数统计求和。

3 ▎混合文本中计算人员个数

如下图所示的数据，B列是参会人员名单，现在需要在C列统计各班参会人数。

C2单元格输入以下公式：

=LEN(B2)-LEN(SUBSTITUTE(B2,'、','')) 1

LEN(B2)取得B2字符串的长度。

LEN(SUBSTITUTE(B2,'、',)) 1，替换掉人名之间的分隔符，也就是顿号，然后再用LEN计算该字符串的长度。最后加1，是因为最后一个人名没有顿号，需要补上。如此一来，每个分隔符就代表一个人名。

用A2数值原有的长度减去被替换掉人名分隔符的长度，也就是人名的个数。

4 ▎混合文本中计算整数最大值

如下图所示的数据，A2单元格是各班人数的数据，现在需要在B2单元格计算各班中最多的人数。

B2单元格输入以下数组公式：

=MAX((SUBSTITUTE(A2,ROW(1:99),'')<>A2)*ROW(1:99))

SUBSTITUTE(A2,ROW(1:99),)<>A2部分，以A2为源字符串，依次将数值1到99替换为空，再把替换后的结果和源字符串进行比较，如果不相等，则证明A2中存在该数值，返回逻辑值TRUE，否则返回逻辑值FALSE。

最后将这组逻辑值TRUE和FALSE，分别乘以被替换的数值（1..99），其中TRUE*数值，返回数值自身；FALSE*数值，返回0；用MAX函数从中取得最大值，即为结果。

5 ▎计算数据最大连续次数

如下图所示的数据，C列是星光足球队的战绩，需要在F2单元格统计连胜的最大次数。

F2单元格公式如下：

=MAX(

(SUBSTITUTE(CONCAT(C2:C15),

REPT('胜利',ROW(1:14)),'')

<>CONCAT(C2:C15))

*ROW(1:14))

PHONETIC(C2:C15)函数将C列战绩合并成一个字符串，作为SUBSTITUTE函数的源文本。

REPT('胜利',ROW(1:14))，将胜利重复1..14次，14是最大可能的连胜次数。

此时思路回到了案例4。SUBSTITUTE函数将REPT函数的结果，在源字符串里替换掉，然后和源字符串比较是否相等。如果源字符串存在替换值，则和原值不相等，返回逻辑值TRUE，否则返回逻辑值FALSE。

最后依然把这一组逻辑值TRUE或FALSE，分别乘以ROW(1:14)，用MAX函数从中取得最大值。

6 ▎将数据按指定分隔符拆分为

如下图所示的数据，B列是多个爱好合并在一起，现在需要把它们按分隔符'/'拆分，结果如C:F列所示。

C2单元格输入以下公式，向下向右复制填充：

=TRIM(MID(SUBSTITUTE($B2,'/',REPT(' ',100)),COLUMN(A1)*100-99,100))

SUBSTITUTE($B2,'/',REPT(' ',100))部分，将源字符串B2单元格数据中的分隔符'/'替换为100个空格。这样就将不同爱好分别划分到了100个空格构建的小房间里。

然后再使用MID函数，按顺序从小房间里取出爱好，最后使用TRIM函数消除多余的空格。该套路我们有一个专门的单章来讲述，可参考函数系列教程文本处理篇的「如何按指定间隔符拆分数据」

有朋友可能会想，函数这么复杂，我还是用分列吧……谁不想回到小时候过无忧无虑的日子呢

嘿！没了，挥一挥胳膊，不带走一根头发。有啥问题可以在VIP会员微信群中提问交流↓👇↓ 挥挥手，咱们明天再见。

本文由公众号“Excel星球”首发。