首页 > Excel专区 > Excel函数 >

excel 提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性

Excel函数 2023-01-08

LEFT、RIGHT、MID这三个可以理解为按字数提取内容,LEFTB、RIGHTB、MIDB这三个可以理解为是按字节提取内容。他们参数都是一样的,就是字数和字节的区别。

▼一、那什么叫字节,单字节、双字节。

字节(Byte )是计算机信息技术用于计量存储容量的一种计量单位。

在半角输入状态(ASCII码),一个英文字母(不分大小写)占1个字节的空间,一个数字占1个字节,一个英文状态的标点符号占1个字节,叫单字节字符;一个中文占2个字节,一个中文标点符号占2个字节,叫双字节字符。

国内、国外绝大部分的系统软件代码都是用半角字符完成的,也就是ASCII码。所以我们在用输入法时一定要默认“半角”状态。

举例说明:图1就是用文本函数LEN和LENB来分别计算字数和单、双字节,更能清楚了解到字节和字数的概念

Excel提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性

图1:字节和字数的区别

▼二、了解完单双字节,现在来了解6个提取文本内容的函数。

他们分别是:LEFT、RIGHT、MID按字数提取内容;LEFTB、RIGHTB、MIDB按字节数提取内容。

① LEFT函数是“从左边开始按要求的字个数来提取内容”,公式模板:LEFT(文本或单元格,提取几个字)。如:=LEFT("我爱中国",2) 返回的结果是“我爱”这两个字。

② RIGHT函数是“从右边开始按要求的字个数来提取内容”,公式模板:RIGHT(文本或单元格,提取几个字)。如:=RIGHT("我爱中国",2) 返回的结果是“中国”这两个字。

③ MID函数是“从要求的指定位置开始,按要求的字个数来提取内容”,公式模板:MID(文本或单元格,从第几个字开始,提取几个字)。如:=MID("我爱美好的生活",3,4) 返回的结果是“美好的生”这四个字。

④ LEFTB函数是“从左边开始按要求的字节数来提取内容”,公式模板:LEFTB(文本或单元格,提取几个字节)。

如1:=LEFTB("我爱中国",5) 返回的结果是“我爱 ”这两个字加一个空格。因为一个中文是2个字节,5个字节表示2.5个中文,因为没有半个中文字,所以用一个空格代替,最终返回就是“我爱 ”。

如2:=LEFTB("我爱1314你",5)返回结果“我爱1”,因为5个字节表示2个中文加1个数字。

⑤ RIGHTB函数是“从右边开始按要求的字节数来提取内容”,公式模板:RIGHTB(文本或单元格,提取几个字节)。如:=RIGHTB("我爱 中国",5) 返回的结果是“ 中国 ”,这一个空格加2个中文。因为空格也算一个字节,一个中文是2个字节。

⑥ MIDB函数是“从要求的字节位置开始,按要求的字节数来提取内容”,公式模板:MIDB(文本或单元格,从第几个字节开始,提取几个字节)。如:=MIDB("我爱美好的?生活",6,8) 返回的结果是“ 好的?生”,这一个空格+两个中文+英文符号?+一个中文。

解析下⑥原因:“我爱美”是6个字节,从第6个字节开始意味着“美”这个字不完整,因为“美”的位置是第5字节和第6字节两个字节位置组合的。字不完整只能用空格代替,代表1个字节。英文符号?也是1个字节,所以8字节刚好是(一个空格+两个中文+英文符号?+一个中文)。

▼三、6个提取内容函数一定要配合查找函数FIND、SEARCH和FINDB、SEARCHB使用才灵活。

FIND、SEARCH返回的是“查找的字”所在的字个数位置,FINDB、SEARCHB返回的是“查找的字”所在的字节数位置,

① 公式模板:=FIND(要查找的内容或单元格,被查的内容或单元格,从第几个字数开始查);

② 公式模板:=SEARCH(要查找的内容或单元格,被查的内容或单元格,从第几个字数开始查);

③ 公式模板:=FINDB(要查找的内容或单元格,被查的内容或单元格,从第几个字节数开始查);

④ 公式模板:=SEARCHB(要查找的内容或单元格,被查的内容或单元格,从第几个字节数开始查);

注意:“要查找的内容”字数一定不能超过“被查的内容”字数,“要查找的内容”一定是在“被查的内容”里的,不然结果是错误值。

如图2:

Excel提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性

图2:查找函数返回的字数位置和字节数位置

▼四、查找函数FIND和SEARCH参数都一样,返回的结果也一样,那这两个函数有什么区别呢?

① 函数 FIND与 FINDB是区分大小写并且不允许使用通配符。

如1:=FIND("a","AAaaA",1)返回结果是3,因为在FIND和FINDB函数眼里:大写的A和小写的a是不一样的,第3参数"1"表示从第1个数开始查找a,返回的结果是第一个a的位置,第一个a是在第3个字数位置。

如2:=FIND("a","AAaaA",4)返回结果是4,第3参数"4"表示从第4个数开始往后面查a,第4个数刚好是小写的a,所以结果返回是4。

如3:=FIND(" ","我爱 你",1)返回的结果是3,因为"我爱"后面有一个"空格",第1参数双引号里也有"空格",返回结果是3;如果第1参数双引号里没有"空格",则返回的结果是第3参数的数字。

FINDB也是同理,从第1字节或者第4字节开始查找。

② 函数 SEARCH与 SEARCHB是不区分大小写并且允许使用通配符。

什么是通配符?通配符是一种特殊语句,主要有星号(*)和问号(?),用来模糊搜索内容。

一个星号(*)可以表示一个或无数个字符;不确定具体有没有内容也可以加星号(*)。如NOTE这个单词,你可以通过*note查找到,也可以通过*te查找。

一个问号(?)仅代表一个字符,而且这个字符必须存在。如NOTE这个单词,你可以通过no?e或者no??来找到,但是note?是找不到的,因为note后面没有内容了;换成note*是没问题的,因为*代表内容可有可无。

总结下FIND和SEARCH区别:就是FIND区分大小写,SEARCH能用通配符,互补关系。FINDB和SEARCHB同理。

▼五、案例讲解

① 案例一、如图3:因为地址不是太规律,第3行有两个“市”字,第4行没有省份,如果想一条公式直接完成,要嵌套很多函数,容易出错,这里用添加辅助列的方法,快速拆分提取内容。

Excel提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性

图3:拆分地址内容

  1. 1 在B2单元格输入公式:=IFERROR(LEFT(A2,FIND("省",A2,1)),"")。因为第四行没有省份,不在外面嵌套IFERROR函数会出现错误值,IFERROR就是可以把错误值变成我们想要的任何值,这里变成空值“”。详见图4:

Excel提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性

图4:提取省份

  1. 2 提取市的内容,这时用到辅助列1。在辅助列F2单元格输入=RIGHT(A2,LEN(A2)-LEN(B2)),再在市列C2单元格输入=LEFT(F2,FIND("市",F2,1))。详见图5:

Excel提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性

图5:提取市内容

  1. 3 提取区县市的内容,这时用到辅助列2。在辅助列G2单元格输入=RIGHT(F2,LEN(F2)-LEN(C2)),再在区县市列D2单元格输入=LEFT(G2,SUM(IFERROR(FIND({"县","区","市"},G2,1),0))),因为是数组模式,一定要按CTRL+SHIFT+回车三键,不然会出错。详见图6:

Excel提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性

图6:提取区县市

  1. 4 提取街道的内容,在E2单元格输入公式=RIGHT(G2,LEN(G2)-LEN(D2)),直接提取街道内容,详见图7:

Excel提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性

图7:提取街道内容

我们通过添加辅助列的方法,就避开了MID这个函数,用更简单的方法提取出了更复杂且不是太规律的地址内容。

② 案例二、提取数字开始往后的内容。在B2单元格输入公式=RIGHTB(A2,LENB(A2)-(SEARCHB("?",A2,1)-1)),利用中文是双字节,数字是单字节来求出数字的位置。详见图8:

Excel提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性

图8:求出数字往后的内容

excel 提取文字数字等内容的8种函数方法,巧妙利用双字节函数特性的下载地址:
  • 本地下载


  • Copyright © 2016-2023 office学习教程网 office.tqzw.net.cn. All Rights Reserved.