今天继续和大家分享这套函数(确实不知道该用哪个量词了,就觉得这个还算贴切点),主要是涉及文本的计数、截取、查找、替换等处理的。相信很多朋友都已经猜到了,那么再让这些函数来亮个相:
注意:由于内容较多,此文要分为几个部分讲解,今天讲解第三部分,也是最后一部分。如果你们要看前两部分的内容,点击下面的链接:
字符串查找定位(字符串的狙击镜)
从上图可以看出,这四个函数的作用、参数几乎一样。那么该怎么区别呢?在实际使用中,该怎么选择呢?我们以上图的函数示例,可以分析出以下几点:
1、SEARCH函数,不区分大小写字母。查找大写字母【O】得到的结果是9,而第9位是小写字母【o】;
2、FIND函数,要区分大小写字母。查找大写字母【O】,其结果为15,正好是大写字母【O】的位置;
3、这4个函数都可以省略第三个参数,即不设置查找的起始位置,则默认按从第1位开始查找;
4、这4个函数都可以查找空白文本,查找空白文本,结果都是1;
5、SEARCH函数支持通配符查找,FIND函数
不支持通配符查找。查找中的通配符有2个:①【?】表示任意单个字符;【*】:表示任意多个字符(字符串);6、关于通配符【?】:用于SEARCHB函数时,表示第一个单字节字符;若用于SEARCH函数,则表示第一个字符。效果如下图。
①例1:查找第一个字符,结果为1;
②例2:查找【?o】,结果为4。此处的【?】代表【r】,组合查找时,返回组合中第1个字符的内容,即【r】在字符串中的位置,结果为4;
③例3:查找【?of】,结果为6。此处的【?】代表【S】,组合查找时,返回组合中第1个字符的内容,即【S】在字符串中的位置,结果为6;
④例4:按字节计算第一个单字节字符在字符串中的位置,结果为5。
接下来,我们看函数的实际应用。
一、提取汉字、字母
前面一章,我们用过这个例子,当时用的LEN和LEB函数组合,计算出汉字和字母的个数。今天我们用SEARCHB函数结合通配符查找的方式,通过定位字母开始的位置(即文字结束的位置),来计算汉字和字母的数量,然后同字符串剪刀把需要的内容截取出来。
二、模糊查找
如上图所示,用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】
字符串替换(字符串的涂改液)
如上图,是这三个函数的基础内容,这三个函数都可以实现将一个字符串中某部分内容替换成新字符串。他们的区别是:
Substitute函数不限定字符串的位置,只限定需替换字符串的内容;按内容替换时,选择此函数
Replace函数不限定字符串的内容,只限定需替换字符串的位置。按位置替换时,选择此函数。
下面我们用案例来看看这两个函数适用于什么情况:
一、将手机号码中间四位替换成【*】以进行保密
大家想想,这个题目,如果用SUBSTITUTE函数,该如何写公式呢?(下面公式还好不长)
<code>=SUBSTITUTE(B92,MID(B92,4,4),"****")/<code>
二、替换文本中的空格
大家想想,这个题目,如果用REPLACE函数,该如何解决呢?(下面公式好复杂,如果再加两个空格呢就更不好处理了)
<code>=REPLACE(REPLACE(B100,FIND(" ",B100),1,),FIND(" ",REPLACE(B100,FIND(" ",B100),1,)),1,)/<code>
三、统计字符串中有多少个空格
先用LEN函数计算带空格的字符串字符数,再用SUBSTITUTE函数清除掉所有空格后,用LEN函数计算无空格字符串的字符数,两个字符数的差就是空格的数量。(此题用REPLACE函数就不好解决了)
字符串重复
上图是函数的基础解释和用法,需注意几个问题:
1、如果重复的次数为0,则返回空白文本;
2、如果重复的次数为小数,则会向下取整后再计算;
3、REPT 函数的结果不能大于 32,767 个字符,否者会显示错误。
下面是这个函数的使用案例:
一、星级评定
二、补齐数字位数
<code>此题用的函数公式为:=RIGHT(REPT(0,4)&C124,4)/<code>
三、条形图制作(类似与数据条)
<code>此题的函数公式为:=REPT("|",C131)&" "&C131/<code>
四、文本拆分【综合应用,经典案例】
<code>公式:=TRIM(MID(SUBSTITUTE(C$130,"-",REPT(" ",50)),ROW(A1)*50-49,50))/<code>
思路:
①用REPT函数生成50个空格组成的字符串;
②用SUBSTITUTE函数批量将【-】替换成50个字符串;
③用MID函数,分别从1、51、101、151……位开始,截取50个字符串的文本内容;
④同TRIM函数将截取的字符串前后的空格清除。
五、数值拆分【会计常用】
此题详见我分享的另一篇文章
此题的解题思路如下:
<code>所用的函数公式:=LEFT(RIGHT(TEXT(ROUND($A3/1%,)," ¥0"),10-COLUMN(A1)))/<code>
到现在,关于文本这一套关于文本的函数就全部介绍完毕。3月5日对这连续3天分享的内容做个简单的总结,希望大家继续关注。
如果你喜欢我分享的内容,请点个赞支持下;
如果你觉得我分享的内容对你有帮助,可以关注我;
如果要看我以前分享过的好玩的内容,大家可以去我的主页查看历史文章。
閱讀更多 上班下班 的文章