各位朋友,你们好。
今天继续和大家分享这套函数(确实不知道该用哪个量词了,就觉得这个还算贴切点),主要是涉及文本的计数、截取、查找、替换等处理的。相信很多朋友都已经猜到了,那么再让这些函数来亮个相:
![acfd4aca07188dae4a443f80a1f1869c.png](https://i-blog.csdnimg.cn/blog_migrate/0a64585840e6f20c287e30267ff68e2f.jpeg)
一套16个函数
注意:由于内容较多,此文要分为几个部分讲解,今天讲解第三部分,也是最后一部分。如果你们要看前两部分的内容,点击下面的链接:
基础篇 进阶篇一
字符串查找定位(字符串的狙击镜)
![eca940e26f59ae3dfb392dfc27dbea08.png](https://i-blog.csdnimg.cn/blog_migrate/48ec61d7962cbd4f53f2d795be5ba6da.jpeg)
字符串查找定位函数
从上图可以看出,这四个函数的作用、参数几乎一样。那么该怎么区别呢?在实际使用中,该怎么选择呢?我们以上图的函数示例,可以分析出以下几点:
1、SEARCH函数,不区分大小写字母。查找大写字母【O】得到的结果是9,而第9位是小写字母【o】;
2、FIND函数,要区分大小写字母。查找大写字母【O】,其结果为15,正好是大写字母【O】的位置;
3、这4个函数都可以省略第三个参数,即不设置查找的起始位置,则默认按从第1位开始查找;
4、这4个函数都可以查找空白文本,查找空白文本,结果都是1;
5、SEARCH函数支持通配符查找,FIND函数不支持通配符查找。查找中的通配符有2个:①【?】表示任意单个字符;【*】:表示任意多个字符(字符串);
6、关于通配符【?】:用于SEARCHB函数时,表示第一个单字节字符;若用于SEARCH函数,则表示第一个字符。效果如下图。
![5925bea9effa3f4306c64c741833b22f.png](https://i-blog.csdnimg.cn/blog_migrate/7dc8f6de527cdd136ff90a6efa858517.jpeg)
通配符在SEARCH、SEARCHB函数中的应用
①例1:查找第一个字符,结果为1;
②例2:查找【?o】,结果为4。此处的【?】代表【r】,组合查找时,返回组合中第1个字符的内容,即【r】在字符串中的位置,结果为4;
③例3:查找【?of】,结果为6。此处的【?】代表【S】,组合查找时,返回组合中第1个字符的内容,即【S】在字符串中的位置,结果为6;
④例4:按字节计算第一个单字节字符在字符串中的位置,结果为5。
接下来,我们看函数的实际应用。
一、提取汉字、字母
![f93779f0427fd7d63162dda93d870fdf.png](https://i-blog.csdnimg.cn/blog_migrate/2a1f7d2754e81fba67595d9e6ebf060d.jpeg)
提取汉字和字母
前面一章,我们用过这个例子,当时用的LEN和LEB函数组合,计算出汉字和字母的个数。今天我们用SEARCHB函数结合通配符查找的方式,通过定位字母开始的位置(即文字结束的位置),来计算汉字和字母的数量,然后同字符串剪刀把需要的内容截取出来。
二、模糊查找
![8a37c3d1b1a7b52ed0b59491ad7c9848.png](https://i-blog.csdnimg.cn/blog_migrate/6fa3728adf29722b52c5755038e86976.jpeg)
模糊查找
如上图所示,用FIND、FINDB、SEARCH、SEARCHB函数,查找关键字,然后在示例文本中获取带有此关键的完整内容。解题原理是利用这四个查找函数时,被查找字符串中没有要查找的内容会返回错误值的特征,再结合LOOKUP函数会自动忽略错误值查找的优势,将数据查找出来,接下来我们以第一组公式为例,来分解求解过程:
LOKUP函数向量用法,先在第2参数中查找小于等于查找值且最接近查找值的数据;然后再从第3个参数的数组中,找到对应位置的数据,返回结果。
1、FIND(C81,B82:B88)={1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
2、-FIND(C81,B82:B88)={-1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
3、LOOKUP函数的查找值为0,然后在第2个结果的数组中,查找小于等于0且最接近0的值,结果为-1(这就是在FIND函数前面添加一个负号的原因,如果FIND函数前面不加负号,可以把LOOKUP函数的第1个参数设置成一个很大的数即可)
4、数字【-1】在第2参数数组的第一个条,则返货第2参数数组的第一个值,结果就是【中国 China】
字符串替换(字符串的涂改液)
![f7495bf7647ab37636a5d847f5fb96eb.png](https://i-blog.csdnimg.cn/blog_migrate/a0f78b3f19512995004e67d88aeba464.jpeg)
字符串替换
如上图,是这三个函数的基础内容,这三个函数都可以实现将一个字符串中某部分内容替换成新字符串。他们的区别是:
Substitute函数不限定字符串的位置,只限定需替换字符串的内容;按内容替换时,选择此函数
Replace函数不限定字符串的内容,只限定需替换字符串的位置。按位置替换时,选择此函数。
下面我们用案例来看看这两个函数适用于什么情况:
一、将手机号码中间四位替换成【*】以进行保密
![bfac20f7be92b04ddfe5dc467ec3d8ed.png](https://i-blog.csdnimg.cn/blog_migrate/ae129b599b5791f9ac2806132a0b9fe3.jpeg)
根据位置替换
大家想想,这个题目,如果用SUBSTITUTE函数,该如何写公式呢?(下面公式还好不长)
=SUBSTITUTE(B92,MID(B92,4,4),"****")
二、替换文本中的空格
![cbcf2044642b016a2305071914ed751f.png](https://i-blog.csdnimg.cn/blog_migrate/c13d875ded020fd2141d7532a153f67c.jpeg)
根据内容替换
大家想想,这个题目,如果用REPLACE函数,该如何解决呢?(下面公式好复杂,如果再加两个空格呢就更不好处理了)
=REPLACE(REPLACE(B100,FIND(" ",B100),1,),FIND(" ",REPLACE(B100,FIND(" ",B100),1,)),1,)
三、统计字符串中有多少个空格
![fd6dbfa6f140d0aec59a568a79c5d73a.png](https://i-blog.csdnimg.cn/blog_migrate/99691b03c0db59e629e90e092717ed0f.jpeg)
统计空格数量
先用LEN函数计算带空格的字符串字符数,再用SUBSTITUTE函数清除掉所有空格后,用LEN函数计算无空格字符串的字符数,两个字符数的差就是空格的数量。(此题用REPLACE函数就不好解决了)
字符串重复
![a73c06823fa9173c6ce08a6aba40a623.png](https://i-blog.csdnimg.cn/blog_migrate/f836ec540fcd11816c82a6532af26515.jpeg)
字符串重复
上图是函数的基础解释和用法,需注意几个问题:
1、如果重复的次数为0,则返回空白文本;
2、如果重复的次数为小数,则会向下取整后再计算;
3、REPT 函数的结果不能大于 32,767 个字符,否者会显示错误。
下面是这个函数的使用案例:
一、星级评定
![0a9571eb902fb95a4756f480fc8e59e5.png](https://i-blog.csdnimg.cn/blog_migrate/ba840157ef5dfa975e11d1c7b61d18ed.jpeg)
二、补齐数字位数
![525bd383a0d9faef659c05c605304a7c.png](https://i-blog.csdnimg.cn/blog_migrate/9c57103f1516f9c5a6b5acbbbbffa5d2.jpeg)
此题用的函数公式为:=RIGHT(REPT(0,4)&C124,4)
三、条形图制作(类似与数据条)
![e96a974ef520a1f33b837c3e63a1682b.png](https://i-blog.csdnimg.cn/blog_migrate/616367db7149be8e94bcaebb5aea23b4.jpeg)
此题的函数公式为:=REPT("|",C131)&" "&C131
四、文本拆分【综合应用,经典案例】
![04b50ef0dc83a11c43f63e4aa0a482ce.png](https://i-blog.csdnimg.cn/blog_migrate/319f79771bc00d0cd4befb2a1891e6e1.jpeg)
文本拆分
公式:=TRIM(MID(SUBSTITUTE(C$130,"-",REPT(" ",50)),ROW(A1)*50-49,50))
思路:
①用REPT函数生成50个空格组成的字符串;
②用SUBSTITUTE函数批量将【-】替换成50个字符串;
③用MID函数,分别从1、51、101、151……位开始,截取50个字符串的文本内容;
④同TRIM函数将截取的字符串前后的空格清除。
五、数值拆分【会计常用】
此题详见我分享的另一篇文章人民币金额拆分
![c84157a30f680715452ad3254cb1e658.png](https://i-blog.csdnimg.cn/blog_migrate/d0c444b92fa794038116d05350703a30.jpeg)
此题的解题思路如下:
![55dd17bbe15b6b2df1976f2f18e37613.png](https://i-blog.csdnimg.cn/blog_migrate/d7221c4b772f083b6a89316311e2d556.jpeg)
所用的函数公式:=LEFT(RIGHT(TEXT(ROUND($A3/1%,)," ¥0"),10-COLUMN(A1)))
到现在,关于文本这一套关于文本的函数就全部介绍完毕。3月5日对这连续3天分享的内容做个简单的总结,希望大家继续关注。
如果你喜欢我分享的内容,请点个赞支持下;
如果你觉得我分享的内容对你有帮助,可以关注我;
如果要看我以前分享过的好玩的内容,大家可以去我的主页查看历史文章。