手把手教你,学会Excel字符串提取

使用LEFT函数和RIGHT函数提取字符串
LEFT函数和RIGHT函数分别以字符串的左/右侧为起始位置,返回指定数量的字符,两个函数的语法相同。
第一参数text为要提取的字符串或单元格引用,第二参数[num_chars]为可选参数,表示要提取的字符数量,省略时默认提取一个字符,即提取字符串最左端或最右端的一个字符。第一参数为文本字符串时,需要用一对半角双引号将其包含,如图10-51所示。
对于需要区分单双字节的情况,可以使用L EFTB和
RIGHTB函数提取字符串,函数语法如下。
“B”代表byte,与LEFT和RIGHT函数的区别是,前者的第二参数为“字符”的数量,无论字符是单字节还是双字节,均按一个字符计算。而加了“B”的LEFTB和RIGHTB函数,第二参数为“字节”的数量。
汉字为双字节字符,字母或数字为单字节字符。如果第一参数最左端或最右端的字符为单字节字符,在省略第二参数时会返回该字符,否则将返回空格,如图10-52所示。
示例10-18  使用LEFT函数提取地址中的城市名称
图10-53中A列为某地址簿中的部分信息,需要在B列将地址中的城市名提取出来。
在B2单元格中输入以下公式,向下复制到B5单 元格。
=LEFT(A2,FIND('市',A2)-1)公式中LEFT函数的第二参数中嵌套FIND函数,FIND(“市”, A2)返回“市”字在A2单元格中的位置,如A2中为“北京市”,则FIND函数返回3。由于最终提取的结果不需要返回“市”字,因此,FIND函数后再减1。
示例10-19  借助LENB函数提取混合内容中的姓名
图10-54中的A列为某公司参与培训人员的姓名及员工号,需要将A列的员工姓名单独提取出来。
本例中的员工姓名字数不等,且姓名后没有可以用 于FIND函数查找的固定字符。通过观察可以发现以下规律:前半部分的员工姓名汉字是双字节字符,而后续的员工号数字是单字节字符。
图10-54 借助LENB函数提取混合内容中的姓名
根据此规律,只要计算出A列单元格中的字符数和字节数之差,就是员工姓名的字符数。再从第一个字符开始,按这个字符数提取,结果即员工的姓名。
在B2单元格中输入以下公式,向下复制到B9单元格。
=LEFT(A2,LENB(A2)-LEN(A2))
其中LENB函数将A2单元格中的每个汉字字节数统计为2,数字字节数统计为1;LEN函数则将所有的字符都按1统计。因此“LENB(A2)-LEN(A2)”返回的结果就是其中汉字的个数。
使用MID函数从单元格任意位置提取字符串
相较于LEFT和RIGHT函数只能从字符串的最左端或最右端开始提取,MID函数在提取字符串的应用中则更为灵活。函数语法如下。
第一参数text为要提取的字符串或单元格引用;第二参数start_num用于指定文本中要提取的第一个字符的位置,即从第几个字符开始提取;第三参数num_chars指定从文本中返回字符的个数。
针对需要按字节数提取的情况,同样可以使用加“B”的MIDB函数。MIDB函数的第二参数和第三参数均指字节数,即从第几个字节开始,提取几个字节。MID和MIDB函数的3个参数均不可省略,如果MIDB函数的第三参数为1,且该位置字符为双字节字符,结果将返回空格,如图10-55所示。
示例10-20  使用MID函数提取字符串中的手机号
图10-56所示的是文字和数字混合的字符串,字符串前后为文本,中间包含的数字是手机号。需要将中间的手机号提取到B列。
在B2单元格中输入以下公式,向下复制到B9单元格。
=MID(A2,FIND('1',A2),11)
本例中提取的手机号都以“1”开头,图10-56 使用MID函数提取字符串中的手机号通过FIND函数找到“1”所在的位置,作为MID函数的第二参数,即返回字符串的起始位置。第三参数为手机号的字符数11。
示例10-21  使用MID函数分列显示答案
图10-57所示的是某次考试选择题部分的答案,需要将B列内容依次提取到C~G列单元格区域。
在C2单元格中输入以下公式,复制到C2:G7单元格区域。
=M ID($B2,COLUMN(A1),1)
公式向右复制时,COLUMN(A1)部分将依次生成递增的自然数序列,作为MID函数的第二参数,即函数提取的起始位置。MID函数在C~G列依次提取B2单元格中的第1~5个字符。
提取身份证信息
我国现行居民身份证号码由18位数字组成,其中第7~14位数字表示出生年月日:7~10位是年,11~12位是月,13~14位是日。第17位是性别标识码,奇数为男,偶数为女。第18位数字是校检码,包括0~9的数字和字母X。使用文本函数可以从身份证号码中提取出身份证持有人的出生日期、性别等信息。
示例10-22  从身份证号中提取出生日期
图10-58为某公司员工信息表的部分内容,需要从B列身份证号中提取出生日期,并且以日期格式存储于C列。
在C2单元格中输入如下公式,向下复制到C9单元格。
=MID(B2 ,7,8)
公式表示从B2单元格中第7位起,一共提取8个字符,得到8位数字的字符串“19790607”,如图10-59所示。
采用分列的方法,将提取到出生日期转换为日期格式,具体操作步骤如下。
步 骤 1
选中C2 :C9单元格区域,按<Ctrl C>组合键复制。保持C2:C9单元格区域的选中状态并右击,在弹出的快捷菜单中选择【选择性粘贴】→【数值】→【确定】选项。
步 骤 2
选中C2:C9单元格区域,在【数据】选项卡下单击【分列】按钮,在弹出的【文本分列向导 - 第1步,共3步】对话框中单击【下一步】按钮,如图10-60所示。
步 骤 3
在弹出的【文本分列向导 - 第2步,共3步】对话框中单击【下一步】 按钮,在弹出的【文本分列向导 - 第3步,共3步】对话框中的【列数据格式】选项区域中选中【日期】复选框,单击【完成】按钮,即可得到需要的结果,如图10-61所示。
示例10-23  从身份证号码中提取性别信息
图10-62为员工信 息,需要从B列身份证号码中提取出性别信息。
在C2单元格中输入以下公式,向下复制到C9单元格。
=IF(MOD(MID (B2,17,1),2),'男','女')
公式中使用MID函数提取身份证号中的第17位数,结果作为MOD函数的第一参数。
再使用MOD函数计算此数值与2相除得到的余数,得到的结果为1或0,即身份证号第17位为偶数时,MOD函数返回0,为奇数时MOD函数返回1。
最后用IF函数判断,MOD函数结果为1时返回“男”,否则返回“女”。
提取字符串中的数字
日常工作中,经常会遇到一些不规范的数据源需要处理,如果数据量较多,在不便于重新录入的情况下,可使用文本函数进行数据的提取。
示例10-24  提取字符串左侧或右侧的连续数字
图10-63所示的A列中字符串包含位数不等的汉字、字母和数字,连续的数字位于字符串的左侧或右侧,需要将字符串中的数字提取到B列。
1. 提取左侧的数字
在“Sheet1”工作表的B2单元格中输入以下公式,向下复制 到B5单元格。
=-LOOKUP(0,-LEFT(A2,ROW($1:$15)))
公式中使用LEFT函数从A2单元格左侧分别提取长度为1~15的文本字符串,再加上一个负号,将数值部分转换为负数,文字部分转换为错误值。
LOOKUP函数以0作为查找值,在由负数和错误值构成的内存数组中返回最后一个负数。最后对LOOKUP函数的结果加上负号,即得到正数结果。
2. 提取右侧的数字
在“Sheet2”工作表的B2单元格中输入以下公式,向下复制到B4单元格。
=-LOOKUP(0,-RIGHT(A2,ROW($1:$15)))
公式与从左侧取值的原理相同,只是将用LEFT函数从字符串左侧取值,变为用RIGHT函数从字符串右侧取值。
示例10-25  提取字符串中间的数字
图10-64中,A列记录结果既包含花费的数额,同时包含花费项目及单位,需要将花费的数额提取至B列。
在B2单元格中输入以下数组公式,按<Ctrl Shift Enter>组合键,向下复制 到B5单元格。
{=-LOOKUP(0,-MID(A2,MATCH(0,0/MID(A2,ROW($1:$99),1),0),ROW($1:$15)))}
“MID(A2,ROW($1:$99),1)”部分分别从A2单元格第1~99位字符开始提取1个字符(此处默认字符数少于99,可根据实际调整),得到由A2单元格中的每一个字符和空文本组成的内存数组。
{买;衣;服;8;0;0;元;'';……;''}
再用0除以这个内存数组,返回由0和错误值构成的新内存数组。
{#VALUE!;#VALUE!;#VALUE!;0;#DIV/0!;#DIV/0!;#VALUE!;……;#VALUE!;}
“MATCH(0,0/MID(A2,ROW($1:$99),1),0)”部分用MATCH函数以0作为查找值,精确定位0在以上内存数组中的位置,返回4。再由MID函数从A2单元格中分别以MATCH函数的返回值4作为起始位置,以ROW($1:$15)作为提取字符长度。
{'8';'80';'800';'800元';……;'800元'}
再加上一个负号,将数值部分转换为负数,文字部分转换为错误值。使用LOOKUP函数,以0作为查找值,返回内存数组中的最后一个负数。最后对LOOKUP函数取负值,即得到正数结果。
使用FIXED函数指定位数舍入数值
在Excel中除了常规的数值舍入函数,使用FIXED函数也可实现按指定小数位数舍入数值的目的,区别是FIXED函数处理的结果为文本型数字。
FIXED函数用于将数字舍入到指定的小数位数,使用小数点和逗号进行格式设置,并返回文本形式的结果。该函数语法为:
第一参数是需要舍入处理的数字或单元格引用。
第二参数可选,是需要保留的小数位数,如果省略则假设其值为2。
第三参数是一个可选逻辑值,如果为TRUE时,则会禁止在返回的文本中包含表示千位分隔符的逗号。
示例10-26  使用FIXED函数将圆面积保留指定小数位
图10-65为某次测量圆板尺寸的部分记录,其中B列是圆板的半径,C列是使用公式计算得到的面积,需要将计算得到的面积保留一位小数。
在D2单元格中输入以下公式,向下复制到D4单元格。
=FIXED(C2,1)
公式中省略 第三参数,如果返回的文本位数大于等于1000,结果将包含表示千位分隔符的逗号。
收款凭证中的数字分列填写
在财务凭证中经常需要对数字进行分列显示,一位数字占用一格,同时还需要在金额前加上人民币符号(¥)。使用Excel制作凭证时,可以利用函数与公式实现金额自动分列。
示例10-27  使用文本函数进行数字分列
图10-66为模拟的收款凭证,其中F列为各商品的合计金额,需要在G~P列利用公式实现金额数值分列显示,且在第一位数字之前添加人民币符号(¥)。
在G5单元格中输入以下公式,将其复制到G5:P9单元格区域。
=IF($F5,LEFT(RIGHT('¥' &$F5/1%,COLUMNS(G:$P))),'')
公式中使用IF函数进行判断,如果F5单元格不为0,则返回LEFT函数提取的结果,否则返回空文本。
LEFT函数中仅有RIGHT函数一个参数,表示从RIGHT函数返回的结果中取值,且只取一个字符(第二参数省略,表示取左侧第一个字符)。
“$F5/1%”部分表示将F5单元格的数值放大100倍,转换为整数,也可以用“$F5*100”来代替。因为分列显示的金额中没有小数点,使用文本函数要对所有的数字包括“角”和“分”一起进行提取,再将字符串“ ¥”(注意人民币符号前有一个空格)与其连接,变成新的字符串“ ¥13600000”。
使用RIGHT函数在这个字符串的右侧开始取值,长度分别为“COLUMNS(G:$P)”部分的计算结果。“COLUMNS(G:$P)”用于计算从公式当前列至P列的列数,计算结果为10。
在公式向右复制时,COLUMNS函数形成一个递减的自然数序列。每向右一列,RIGHT函数的取值长度减少1,即G5单元格中公式RIGHT函数取值长度为“COLUMNS(G:$P)”,结果为10位,H5单元格为“COLUMNS(H:$P)”,结果为9位。
如果RIGHT函数指定要截取的字符数超过字符串总长度,结果仍为原字符串。“RIGHT(' ¥13600000', 10)”的结果为“ ¥13600000”,最后使用LEFT函数取得首字符,结果为空格。
人民币符号(¥)之前加空格是为了保证当截取字符数超过字符串总长度时,RIGHT截取到的结果最左侧的字符为空格,这样所有未涉及金额的部分都将显示为空白。
(0)

相关推荐