总结篇--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函数七兄弟,个个本领大!

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

来,平均一下!

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

遇到不规范的数据录入,你该怎么办?

戳原文,更有料!免费模板文档!

(0)

相关推荐

  • 折腾了3天,我整理出史上最全的文本函数用法案例,实用到爆!(建议收藏)-第二期

    每天一点小技能 职场打怪不得怂 编按:昨天,我们学习了九大文本函数系列案例用法的第一期--<最魔性的文本函数,75%的Excel人都被它坑过,或许下一个轮到你-->.今天,我们继续学习文本 ...

  • 有一个Excel函数叫:省时一整天,你却连我的名字都不知道…

    ★ 编按 ★ Hello大家好,今天给大家介绍一下SUBSTITUTE函数,它可以将数据中的旧值替换为新值,与我们熟悉的"查找替换"功能相似.很多小伙伴可能会说,简单的查找替换就能 ...

  • SUBSTITUTE函数用法全总结

    HI,大家好,我是星光. 今天给大家分享一个函数,叫做SUBSITUTE. SUBSITUTE函数是Excel文本处理中最常使用的函数之一,可以将数据局部的旧值替换为新值,类似于基础操作里的查找与替换 ...

  • 四大经典案例带你玩转Excel必会函数之Substitute函数

    各位早,我是小雅! 先通知一件事情:今晚8点滴答老师的Excel免费大型公开课,欢迎大家来听课交流. 关于今晚上课教室以及今天教程配套的Excel练习文件,请扫下面二维码加入微信群领取. 温馨提醒:已 ...

  • 涨知识!原来SUBSTITUTE函数这么有用!必须学

    在Excel中,SUBSTITUTE函数鲜为人知,她是一个替换函数,在Excel中也是一个非常牛牛牛的函数,下面一起来看下这个替换函数SUBSTITUTE的使用技巧,快来学习! 1.基础用法 在一个文 ...

  • 总结篇--SUBSTITUTE函数实用终极帖

    来源:Excel应用之家 SUBSTITUTE函数是EXCEL常用的文本函数之一,常用于字符串的提取等操作.今天就让我们再一次来重温一下他都有哪些用法吧! 01 文本替换 下例中,以"金庸& ...

  • 总结篇--COUNTIF函数实用终极帖

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! COUNTIF函数主要用于统计满足某个条件的单元格的个数.其基本用法已经在 ...

  • 总结篇--SUMIF函数实用终极帖

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 朋友们今天向大家介绍一期SUMIF函数的应用技巧. 这个函数用于对区域中符 ...

  • 总结篇-LOOKUP函数实用终极帖

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 在EXCEL的查找函数中,除了我们都知道的大名鼎鼎的VLOOKUP函数外, ...

  • 总结篇-IF函数实用终极帖

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! IF函数就是EXCEL界的初恋情人.每一个学习EXCEL的人,十有八九最先 ...

  • 总结篇--INDIRECT函数使用终极帖

    蓝色 动手操作是熟练掌握EXCEL的最快捷途径! 在EXCEL中有这样一个特别的函数--INDIRECT函数,它的功能是间接引用.然而,在做好本职工作的前提下,INDIRECT函数在其它方面的应用发挥 ...

  • 总结篇--FREQUENCY函数使用终极帖

    来源:Excel应用之家 很多小伙伴们可能是第一次听说FREQUENCY这个函数.FREQUENCY这个函数是EXCEL最重要的函数之一,掌握了它,你就掌握了打开更高水平之门的钥匙.FREQUENCY ...

  • 总结篇--TEXT函数使用终极帖

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 在如何转换[中文大写金额]这篇帖子中我简单介绍了TEXT函数的语法结构,并 ...

  • 总结-VLOOKUP函数实用终极帖

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! VLOOKUP函数是EXCEL中非常重要的使用频率非常大的一个函数.之前我 ...