![aa5a258a921c8f65f17c0a702fde7f33.png](https://i-blog.csdnimg.cn/blog_migrate/d4e7255dbd626318cd6495f3b533f489.jpeg)
![9212c37e78fd19d8640ba53a52449dfa.png](https://i-blog.csdnimg.cn/blog_migrate/05244835cea4e3e4e22fb147ae9efff2.jpeg)
SUBSTITUTE函数是EXCEL常用的文本函数之一,常用于字符串的提取等操作。
关于SUBSTITUTE函数的基本使用,请参看帖子Substitute函数使用简介。今天就让我们再一次来重温一下他都有哪些用法吧!
01 文本替换
下例中,以“金庸”来替代“黄药师”。
![8e49c811de5929d2572d8ab4c8d2da62.png](https://i-blog.csdnimg.cn/blog_migrate/4c450a88942bd8735d923dba170def4c.jpeg)
在单元格B2中输入“=SUBSTITUTE(A2,"黄药师","金庸")”并向下拖曳即可。
思路:
- 公式比较简单,直接用新文本替换旧文本即可
02 去除空格
和上例文本替换相类似,请看下例。
![f200c49732be982c3f8b284302f5005f.png](https://i-blog.csdnimg.cn/blog_migrate/9e7701860032f1d28f5cbb2a1558a54d.jpeg)
在单元格B2中输入“=SUBSTITUTE(A2," ","")”即可。
03 隐藏数字
例如,我们隐藏手机号码中间的5位数字。
![942f01d7fcf16cee2d02f8d988e4bb1c.png](https://i-blog.csdnimg.cn/blog_migrate/cf716224b754b24bdc131baba1cecd59.jpeg)
在单元格B2中输入“=SUBSTITUTE(A2,MID(A2,4,5),"*****")”并向下拖曳即可。
思路:
- 利用MID函数提取手机号中间的5位数字
- 利用SUBSTITUTE函数进行替换
其实这个功能还可以用REPLACE函数来实现。朋友们还记得吗?Replace函数使用简介。
04 统计人数
有些情况下,我们登记的信息是下面这样子的。这时候一般的统计方法是难以实现的。
![ea415332ca457429ecf1f4b3e11b3493.png](https://i-blog.csdnimg.cn/blog_migrate/c20c8e92fe25f7edc940975e972d8f65.jpeg)
在单元格B2中输入“=LEN(A2)-LEN(SUBSTITUTE(A2,"、",""))+1”并向下拖曳即可。
思路:
- 利用SUBSTITUTE函数用“”把顿号替换
- 利用LEN函数计算出替换前和替换后的字符长度
- 相减便是顿号的个数
- 加上1便是人员的数量
05 求和
有时候文本的输入并不是那么的规范,例如下列。怎样求和呢?
![847a123b01654debd8f90acb8c12b4c4.png](https://i-blog.csdnimg.cn/blog_migrate/5c62a97b856f6dcb73767275b6215ec7.jpeg)
在单元格C2中输入“=SUM(--SUBSTITUTE($A$2:$A$4,"人",""))”,CTRL+SHIFT+ENTER三键回车即可。
思路:
- 利用SUBSTITUTE函数,将单元格区域A2:A4中的“人”用空“”替换
- 减负运算,将文本型数字转换为数字型数字
- SUM函数求和
06 分列
利用SUBSTITUTE函数我们也可以拆分数据。
![8fbb75064b2f7c56e8c98c8d3731964e.png](https://i-blog.csdnimg.cn/blog_migrate/229ff537c8f30c0f9bcc25f3324a4317.png)
在单元格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函数的经典用法。
![9212c37e78fd19d8640ba53a52449dfa.png](https://i-blog.csdnimg.cn/blog_migrate/05244835cea4e3e4e22fb147ae9efff2.jpeg)
在单元格B2中输入“=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",99)),99,99))”即可。
思路:
- 利用SUBSTITUTE函数将"/"用重复了99次的空格替换
- 利用MID函数从第99位提取数值,长度为99个字符
- 最后利用TRIM去除多余的空格得到数值
注意,为什么用99呢?其实是为了重复的空格长度要大于字符的长度,确保能够提取到想要提取到的数据。
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
http://weixin.qq.com/r/Dy1FXc3Em5DkrYk_93gd (二维码自动识别)