手把手教你，学会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截取到的结果最左侧的字符为空格，这样所有未涉及金额的部分都将显示为空白。