这个函数看起来很简单，却是被严重低估了（附练习文件）

点击蓝字【秋叶 Excel】👆

发送【交流】

立即进秋叶 Excel 读者快乐交流群！

本文作者：小花

本文编辑：竺兰

留言区获取练习文件

👇👇👇

Excel 函数的魅力就在于，每一个简单的函数吃透了，就一定有别样的精彩。

SUBTITUTE 函数就是一个典型的例子，高手眼中，它可不只是一个文本替换函数。

与 REPLACE 函数替换某一文本字符串中指定位置处的任意文本不同，SUBSTITUTE 函数可以在某一文本字符串中替换任意指定的文本。

基础应用：删除指定文本

举个例子，要将房源编码「1 号住宅楼-101」简化为「1-101」，我们需要将房源编码中的「号住宅楼」删除，使用 SUBSTITUTE 函数将「号住宅楼」替换为空即可实现。

B2 单元格公式如下：

=SUBSTITUTE(A2,"号住宅楼",)

👉 公式说明：

SUBSTITUTE 函数的第三个参数为空，表示将旧文本替换空；第四个参数省略，表示将 A2 文本中的所有「号住宅楼」旧文本全部替换为空，其最终效果就是删除旧文本。

了解了 SUBSTITUTE 函数的基本用法后，我们再来看看，替换指定文本这一基本功能是如何被用到极致的？

初级应用：插入字符

以上案例中，SUBSTITUTE 将旧文本替换为空即为实现删除指定文本，那如果将旧文本替换为新文本+旧文本，是否可以实现插入功能呢？答案是肯定的。

例如，我们使用 SUBSTITUTE 函数将「-」替换为「-B2-」，从而实现在部门和姓名中插入职位。

C2 单元格公式如下：

=SUBSTITUTE(A2,"-","-"&B2&"-")

加强应用：文本换行

同样的思路，如果将指定旧文本替换为换行符，便可以实现在指定文本或符号处自动换行显示。

这里需要说明的是，换行符无法直接输入到函数中，因此我们使用 CHAR(10)来获得换行符。

PS：CHAR 函数的作用是根据代码数字制定的字符，数字 10 对应的字符就是换行符。

B2 单元格公式：

=SUBSTITUTE(A2,"，",CHAR(10))

进阶应用：混合文本计数

在填列名单时，有些人习惯于将全部人名填到一个单元格中，用特定符号隔开，这就增加了计算人数的难度。

还好有 SUBSTITUTE 函数，只需将特定符号替换为空，再计算替换前后的文本字符数之差，就可轻松计算人数。

例如，如下图，求每组的名单人数。

C2 单元格公式如下：

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

👉 公式说明：

LEN 函数可以计算出文本中的字符个数，观察名单文本特点不难发现，替换前后的字符格式之差+1 即为人数。

高阶应用：去单位求和

工作中，我们经常遇到将数值和单位填到同一单元格内的情况，此时求和就变得非常困难了。但如果你会 SUBSTITUTE 的升级应用，那将不再是问题。

举个例子，如下图，我们需要根据销售面积和价格都带单位的数据求出销售金额，此时我们就需要使用 SUBSTITUTE 将指定单位文本去除后再进行求和。

B8 单元格公式如下：

=SUMPRODUCT(SUBSTITUTE(B2:B6,"平",)*SUBSTITUTE(C2:C6,"万",))

▲ 左右滑动查看

👉 公式说明：

SUMPRODUCT 引导两个数组乘积和运算，两个数组均由 SUBSTITUTE 函数计算得来。

SUBSTITUTE 分别将面积单位「平」和价格单位「万」替换为空，即删除单位，得到两个文本型数字组成的数组，在 SUMPRODUCT 函数中，乘法可以将文本型数字转化为数值型数组，从而实现相乘并求和。

神级应用：最大值提取

如果上述五种应用你都能掌握，那么恭喜你，SUBSTITUTE 函数你已几乎可以轻松拿捏。

以下谈到的神级应用，功力不深者切勿挑战哦，以免误伤脑细胞！

以下案例中，我们要从包含成绩的混合文本中提取三科成绩最高分，需要用到 MAX 函数、 ROW 函数与 SUBSTITUTE 函数来构建数组公式。

C2 单元格公式如下：

{=MAX((SUBSTITUTE(B2,ROW(1:150),)<>B2)*ROW(1:150))}

▲ 左右滑动查看

👉 公式说明：

❶ ROW(1:150)返回一个 1-150 的数组，

❷ SUBSTITUTE 将 B2 文本中数字 1 到 150 分别替换为空（即删除该数字）。

❸ 对 1-150 中的任一数字来说，如果 B2 中的文本不包含该数字，则文本保持不变，逻辑判断式SUBSTITUTE(B2,ROW(1:150),)<>B2 不成立，返回 FALSE，即为 0，0*ROW(1:150)=0；

反之，B2 文本中的该数字被删除，则逻辑判断式：SUBSTITUTE(B2,ROW(1:150),)<>B2 成立，返回 TRUE，即为 1。

❹ 1*ROW(1:150）=ROW(1:150），即返回该数字本身。

于是，B2 文本中包含的所有数字都被提取出来，此时用 MAX 就可以轻松提取最大值。

注意，该公式只能用于提取最大值，将 MAX 替换为 MIN 并不能正确提取最小值，且该公式输入后需按【Ctrl+Shift+Enter】来完成数组运算。

SUBTITUTE 的高难度应用还有很多，比如混合文本拆分、求最大连续次数，就连下图这样的罗列型数字求和，也可以用 SUBSTITUTE 函数来实现。

可以说，研究有多深入，SUBSTITUTE 函数就有多精彩！

=EVALUATE(SUBSTITUTE(文本求和!B2,"、","+"))

▲ 左右滑动查看

公式说明：哈哈，没有说明，不妨请你开动脑筋琢磨一下，再留言与我们交流吧！

总结一下

以上，就是小花对 SUBSTITUTE 函数的一些研究心得，包括：

❶ 基操级：从替换、删除、插入到换行

❷ 强化级：文本计数和去单位求和

❸ 烧脑级：混合文本多数字取最大值、罗列型数字求和

你学会了吗？你觉得哪个用法让你心动，哪个用法让你眼晕，不妨留言与我们交流吧！

欢迎加入秋叶 Excel 专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率

↓↓↓

遇到有价值的文章

不放过 !

👇👇👇

动动小手

分享给朋友~

👇👇👇