总结篇--SUBSTITU函数实用终极帖

点击上方

蓝色

文字  关注我们吧！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

SUBSTITUTE函数是EXCEL常用的文本函数之一，常用于字符串的提取等操作。

关于SUBSTITUTE函数的基本使用，请参看帖子Substitute函数使用简介。今天就让我们再一次来重温一下他都有哪些用法吧！

01

文本替换

下例中，以“金庸”来替代“黄药师”。

在单元格B2中输入“=SUBSTITUTE(A2,"黄药师","金庸")”并向下拖曳即可。

思路：

• 公式比较简单，直接用新文本替换旧文本即可
  

02

去除空格

和上例文本替换相类似，请看下例。

在单元格B2中输入“=SUBSTITUTE(A2," ","")”即可。

03

隐藏数字

例如，我们隐藏手机号码中间的5位数字。

在单元格B2中输入“=SUBSTITUTE(A2,MID(A2,4,5),"*****")”并向下拖曳即可。

思路：

• 利用MID函数提取手机号中间的5位数字

• 利用SUBSTITUTE函数进行替换

其实这个功能还可以用REPLACE函数来实现。朋友们还记得吗？Replace函数使用简介。

04

统计人数

有些情况下，我们登记的信息是下面这样子的。这时候一般的统计方法是难以实现的。

在单元格B2中输入“=LEN(A2)-LEN(SUBSTITUTE(A2,"、",""))+1”并向下拖曳即可。

思路：

• 利用SUBSTITUTE函数用“”把顿号替换

• 利用LEN函数计算出替换前和替换后的字符长度

• 相减便是顿号的个数

• 加上1便是人员的数量

05

求和

有时候文本的输入并不是那么的规范，例如下列。怎样求和呢？

在单元格C2中输入“=SUM(--SUBSTITUTE($A$2:$A$4,"人",""))”，CTRL+SHIFT+ENTER三键回车即可。

思路：

• 利用SUBSTITUTE函数，将单元格区域A2:A4中的“人”用空“”替换

• 减负运算，将文本型数字转换为数字型数字

• SUM函数求和

06

分列

利用SUBSTITUTE函数我们也可以拆分数据。

在单元格C2中输入“=LEFT(A2,SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))”，CTRL+SHIFT+ENTER三键回车即可。

思路：

• 用空字符分别替代字符串中的数字0~9。

• 分别计算替代后的新的字符串的长度。

• 用替代前的字符串的长度减去替代后的字符串的长度，实际得出的是每个数字的个数。

• 将以上这些数字的个数相加，其和就是字符串中数字的位数。

• 用left函数将数字从字符串中提取出来。

• 最后用right函数将文本提取出来。

07

经典应用

这个用法是SUBSTITUTE函数的经典用法。

在单元格B2中输入“=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",99)),99,99))”即可。

思路：

• 利用SUBSTITUTE函数将"/"用重复了99次的空格替换

• 利用MID函数从第99位提取数值，长度为99个字符

• 最后利用TRIM去除多余的空格得到数值

注意，为什么用99呢？其实是为了重复的空格长度要大于字符的长度，确保能够提取到想要提取到的数据。

-END-

推荐阅读：

IF函数七兄弟，个个本领大！

一对多查询经典函数组合拓展应用--多对多查询

来，平均一下！

总结篇--反向查找函数使用终极帖

遇到不规范的数据录入，你该怎么办？

戳原文，更有料！免费模板文档！