运用Excel进行职场办公，我们会经常遇到这样的问题：如何从包含字母、汉字和符号的不规则字符串提取固定目标数字例如电话号码？熟悉Excel的小伙伴应该都了解常规的字符提取函数LEFT、RIGHT和MID，除了这几个常用函数外，灵活运用查询函数LOOKUP和VLOOKUP也能达到提取数字的目的。下面，小包老师来给大家详细的介绍多种从字符串中提取目标数字的重要方法。

文章最后有彩蛋！好礼相送！

一、数字在字符串的左边

1.LEFT函数常规用法提取手机号码

如果要提取的手机号码在字符串最左边，我们可以直接利用LEFT函数进行提取，语法为：=LEFT(字符串，[字符个数])，它表示从左边开始提取指定个数的字符。如图1所示，要提取电话号码，在B10单元格输入公式：=LEFT(A10,11)，向下填充即可。Excel速成第13节：MID、RIGHT、LEFT字符提取和LEN、LENB字符长度

图1

1.1号码在字符串左边，且字符串只有数字和汉字组成

如图1-1所示，号码在字符串左边，且字符串只有数字和汉字组成，公式为：=MID(A2,SEARCHB("?",A2),LEN(A2)*2-LENB(A2))，通配符“？”和SEARCHB函数组合表示查找第一个字节即手机号码第一个数字，LEN(A2)*2-LENB(A2)可以得出号码的字符长度，思路是LENB(A2)-LEN(A2)得到汉字的字符个数，再用LEN(A2)-（LENB(A2)-LEN(A2)）即字符串字符个数减去汉字字符个数得出手机号码字符个数，LEN(A2)-（LENB(A2)-LEN(A2)）即为LEN(A2)*2-LENB(A2)。

图1-1

当然，此公式可以将MID函数替换为LEFT函数，公式为：=LEFT(A2,LEN(A2)*2-LENB(A2))。

1.2字符串长度一致，姓名或号码位置一致

如上例所示，字符串长度一致，姓名和号码位置一致，可以使用数据——分裂——固定宽度——在姓名和号码之间设置分裂线，点击下一步确定即可，将姓名和号码拆分出来，如图1-2所示，结果如图1-2-1所示。若姓名在前，号码在后，或者号码在中间，两边汉字长度一致，运用分裂方式也可以，只要满足字符长度一致和结构一致即可。

图1-2

图1-2-1

2.MID函数常规用法提取手机号码

提取的手机号码在字符串最左边，常用的还有MID函数，它表示从一个字符串的指定位置开始提取指定个数的字符，其语法为：=MID(字符串，从哪个位置提取，提取的字符个数），如图2所示，在B10单元格输入公式：=MID(A10,1,11)，向下填充即可。

图2

2.1字符串长度不一致，号码位置不一致

如图2-1所示，信息列中字符串长度不一致，号码位置不一致，但无其他符号只有数字和汉字组成，可以使用MIDB+SEARCHB函数，公式为：=MIDB(A2,SEARCHB("?",A2),LEN(A2)*2-LENB(A2))。此方法可以归纳为：在一个字符串当中提取一个字节的字符。

图2-1

3.VLOOKUP函数提取（号码在最左边，且后面无数字）

看到这里，相信很多小伙伴会很疑惑，既然有简单的方法，为什么还要学习复杂的方法?很多时候，你与大神的差距，在于大神掌握多种方法，而你只会一两种，有时候遇到复杂的情况，你的这一两种方法就不顶用了。另外，通过这样的方式，大家更容易理解VLOOKUP/LOOKUP函数的深刻内涵。VLOOKUP函数最全面最详细的讲解大全，涵盖17个重要和常见用法！

VLOOKUP函数高阶用法可以提取数字，语法为：=VLOOKUP(查找值,数据区域,列序数,[匹配方式])，如图3所示，我们先用LEN函数计算出要提取数字的字符串的长度，最大字符长度是33，再用VLOOKUP函数：=VLOOKUP(9E+307,MID(A10,ROW($1:$33),11)*1,1,TRUE)，按下Ctrl+Shift+Enter。

图3

ROW($1:$33)构建1到33组成的数组{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33}，用MID函数将字符串按照11个字符长度分别提取33次，得到包含"15687895666"的数组，后面再乘以1，将文本型数值转化为数值型，其他的转化为错误值，得到结果包含15687895666和很多#VALUE!组成的数组。

这个数组，我们发现，除了电话号码，其余均为错误值，我们如何利用VLOOKUP函数将电话号码提取出来呢？我们可以利用VLOOKUP函数的近似匹配原则将号码取出，VLOOKUP函数的第四参数我们用TRUE表示近似匹配，当查找值不存在，会匹配到数据区域中最后一个与查找值类型相同且比查找值小的值，这里查找值我们用Excel总最大的数值9E+307表示，任何电话号码都比9E+307要小。需要注意的是，这个公式有一个漏洞，它必须适用于字符串的最后面没有数字，如案例所示，字符最后面用东莞市或WX或@结尾。如图4所示，第二个字符串后面若为数字，那么结果返回7，就会出错！

图4

4.VLOOKUP函数提取（号码在最左边，后面也有数字）

上文出现的漏洞，如何避免呢？即字符串左边为号码，但字符串后面以数字结尾，同样用VLOOKUP函数，如图5所示，公式可以稍作修改：=VLOOKUP(0,MID(A10,ROW($1:$33),11)*{0,1},2,0)，同样要按下Ctrl+Shift+Enter。

图5

观察这个公式，MID(A10,ROW($1:$33),11)不是乘以1而是乘以数组{0,1}，得到结果为0,15687895666和很多#VALUE!,#VALUE!组成的两列数组，第一参数我们用0,0在数组中重复出现多次，依据VLOOKUP函数的特性，当查找值重复的时候，会匹配到第一个重复值即第一个0，第一个0对应的数值为15687895666，将第三参数设置为2，第四参数设置为0（精确匹配），就可以返回号码。

5.VLOOKUP+LEFT组合函数提取数字

如图6所示，号码在字符串左边，后面有以数字结尾和无数字结尾，可以利用

VLOOKUP+LEFT组合，其公式为：=VLOOKUP(9E+307,LEFT(A10,ROW($1:$33))*1,1,TRUE)，按下Ctrl+Shift+Enter。原理就不在赘述了，与上述差不多。

图6

6.LOOKUP函数提取数字(号码在左边，字符串后面无数字）

VLOOKUP函数可以提取数字，LOOKUP自然也可以，语法为=LOOKUP(查找值，查找向量，[返回向量])，返回向量省略时与第二参数一致。如图7所示，其公式为:=LOOKUP(9E+307,MID(A10,ROW($1:$33),11)*1)，同样按下Ctrl+Shift+Enter。LOOKUP查询函数表示VLOOKUP和XLOOKUP都是渣！它的强大超乎想象！

图7

MID(A10,ROW($1:$33),11)*1得到15687895666和很多#VALUE!组成的数组，查找值设置为9E+307，在查找向量中找不到时，会匹配到查找向量即数组中最后一个数值，唯一的数值即15687895666(其余均为错误值）。

7.LOOKUP函数提取数字（号码在左边，字符串后面有数字）

如图8所示，不管字符串最后面有没有数字，都可以用LOOKUP+LEFT提取，公式为：=LOOKUP(9E+307,LEFT(A10,ROW($1:$33))*1)，最后按下Ctrl+Shift+Enter。

图8

8.MID/LEFT+CONCAT+IFERROR+MID提取数字

提取数字的方法有很多，只要大家深刻的理解函数，这里小包老师再给大家介绍一种，如图9所示，公式为：=MID(CONCAT(IFERROR(--MID(A10,ROW($1:$33),1),"")),1,11)，或者：=LEFT(CONCAT(IFERROR(--MID(A10,ROW($1:$33),1),"")),11)，同样按下Ctrl+Shift+Enter。

图9

MID(A10,ROW($1:$33),1)可以将字符串中的每一个字符都单独提取出来，前面加双负号“--”，可以将文本型数值转化为数值型数据，非文本型数值都转化为错误值，结果为：{1;5;6;8;7;8;9;5;6;6;6;#VALUE!;#VALUE!;#VALUE!;4;3;5;4;6;5;6;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}，然后用IFERROR函数将数组中的错误值转化为空值，在利用CONCAT字符连接函数将数字和空值组合起来结果为："156878956664354656"，最后用LEFT或MID函数提取这个数值即可。

二、数字在字符串的右边

1.RIGHT函数提取数字

RIGHT函数语法：=RIGHT(字符串，[字符个数])，表示从字符串的右边开始提取指定位数的字符，如图10所示，公式为：=RIGHT(A10,11)。

图10

2.MID+FIND组合函数提取数字

FIND为Excel中的字符定位函数，语法为：=FIND(要查找的字符串，被查找的字符串，[从哪个位置开始查找])，如图11所示，电话号码在字符的最右边，公式为：=MID(A10,FIND("电话",A10)+2,11)或者=MID(A10,FIND(1,A10),11)。Excel速成教程第14节：FIND和FINDB区别？SEARCH和SEARCHB的区别？

图11

如果手机号码前面都有“电话”或者其他固定汉字、符号等，可以直接用FIND函数定位“电话”或其他汉字、符号的位置，在后面加2，即为电话号码第一位出现的位置，然后结合MID函数提取出电话即可。如果没有“电话”二字或有的有有的没有那么可以直接定位“1”，所有手机号码都是以“1”为开头的（必须保证字符串中只有一个1）。

2.VLOOKUP+RIGHT函数提取

如图12所示，公式为：=VLOOKUP(9E+307,RIGHT(A10,ROW($1:$32))*1,1,TRUE)，按下Ctrl+Shift+Enter。

图12

RIGHT(A10,ROW($1:$32))分别将字符串按照字符个数从1到32提取出32次，结果乘以1，将文本型数值数值转化为数值型，第一参数设置为9E+307，查找值不存在，可以匹配到数据区域中最后一位数值。

3.VLOOKUP+MID函数提取

如图13所示，公式为：=VLOOKUP(0,MID(A10,ROW($1:$32),11)*{0,1},2,0)，按下Ctrl+Shift+Enter。

图13

MID(A10,ROW($1:$32),11)将字符串按照11个字符的长度提取出32次，乘以数组{0,1}，得到0和数值以及错误值对错误值这样的两列数据组成的数组，第一参数设置为0，查找区域（数组）中的0有多个，当0存在重复，默认匹配数组中的第一个0。

4.LOOKUP+RIGHT函数提取

如图14所示，利用LOOKUP+RIGHT函数提取手机号码，其公式为：=LOOKUP(9E+307,RIGHT(A10,ROW($1:$32))*1)，按下Ctrl+Shift+Enter。

图14

5.MID/LEFT+CONCAT+IFERROR+MID提取数字

如图15所示，用MID/LEFT+CONCAT+IFERROR+MID提取数字，公式为：=MID(CONCAT(IFERROR(--MID(A10,ROW($1:$32),11),"")),1,11)或=LEFT(CONCAT(IFERROR(--MID(A10,ROW($1:$32),11),"")),11)，都要按下Ctrl+Shift+Enter。

图15

IFERROR(--MID(A10,ROW($1:$32),11),"")得到数组{"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";15687895666;5687895666;687895666;87895666;7895666;895666;95666;5666;666;66;6;""}，数组中的“”是利用IFERROR将错误值转化为空值得到的,而错误值是通过双--得出的（文本型数据会转化为错误值），利用CONCAT函数连接数组元素得到"156878956665687895666687895666878956667895666895666956665666666666"，这个字符串的头11为即为号码，再用MID或LEFT提取即可。

三、数字在字符串的中间

1.MID/LEFT+CONCAT+IFERROR+MID提取数字

MID/LEFT+CONCAT+IFERROR+MID提取数字，不管数字（号码）在字符串的左边、右边还是中间，都可以通过此方法提取，如图16所示，公式为：=MID(CONCAT(IFERROR(--MID(A10,ROW($1:$32),11),"")),1,11)或者=LEFT(CONCAT(IFERROR(--MID(A10,ROW($1:$32),11),"")),11)，都要按下Ctrl+Shift+Enter。原理上述已讲解，不再重复了。

图16

2.FIND+MID提取数字

如图17所示，字符串中只有一个"1"，那么可以用：=MID(A10,FIND(1,A10),11)，因为手机号码默认11位，所以MID函数的第三参数为11。

图17

如果手机号码前面都带“电话”两个字，可以用公式：=MID(A10,FIND("电话",A10)+2,11)，之所以+2，是因为号码在“电”字后两位。

3.VLOOKUP+MID函数

VLOOKUP+MID组合函数提取数字，不管数字（号码）在字符串的左边、右边还是中间，都可以通过此方法提取，如图18所示，公式为：=VLOOKUP(0,MID(A10,ROW($1:$32),11)*{0,1},2,0)，按下Ctrl+Shift+Enter。

图18

4.CTRL+E智能填充

Excel2013版本之后，可以利用快捷键CTRL+E智能填充，不管数字（号码）在字符串的左边、右边还是中间，都可以通过此方法提取，如图19所示，先将第一个字符串中的号码复制粘贴到B10单元格，选中B10：B12，按快捷键Ctrl+E,即可智能填充号码。

图19

5.数字两侧有特殊符号如#可用MID+SUBSTITUTE

若数字两侧存在特殊符号如“-”或“~”或“#”等，可以使用MID+SUBSTITUTE,如图19-1所示，公式为：=TRIM(MID(SUBSTITUTE(A2,"#",REPT(" ",100)),100,100))，先用SUBSTITUTE将字符串中的“#”都替换成100个空格，注意“ ”中间有一个空格不是“”（空值），再用MID函数提取包含前后空格的数字，最后用TRIM函数清除字符串中的多余空格。

图19-1

四、数字被打乱分散的分布在字符串中

实际工作当中，我们遇到的情况可能更加复杂，号码不是连续的出现在字符串的左侧、右侧或中间，而是完全被打乱，混乱的分散在字符串中，这该如何解决呢？

1.CONCAT+IFERROR+MID万能提取组合

CONCAT+IFERROR+MID是Excel中的万能提取组合，如图20所示，公式为：=CONCAT(IFERROR(--MID(A10,ROW($1:$26),1),""))，然后按下Ctrl+Shift+Enter。

图20

MID(A10,ROW($1:$26),1)将每一位字符都提取出来，用双负号使非数值转化为错误值，文本型数值转化为数值，IFERROR将错误值转化为空值，再用CONCAT函数连接所有数值{1;3;3;"";"";2;3;4;"";"";"";"";"";4;"";"";"";"";"";"";"";"";5;6;7;7}。

2.LOOKUP函数

LOOKUP函数提取不规则分布数字，实质上也利用了万能提取组合，如图21所示，公式为：=LOOKUP(9E+307,--CONCAT(IFERROR(MID(A10,ROW($1:$26),1)*1,"")))，然后按下Ctrl+Shift+Enter。

图21

CONCAT函数连接所有数值，得到的结果是一个文本型数值，所以要在前面添加“--”，将其转变为数值型，以此来匹配数值型查找值9E+307，查找值不存在，会匹配到第二参数中的最后一位数值。

3.VLOOKUP函数

同样的，也是利用到了万能组合函数，如图22所示，公式为：=VLOOKUP(0,CONCAT(IFERROR(MID(A10,ROW($1:$26),1)*1,""))*{0,1},2,0)，然后按下Ctrl+Shift+Enter。

图22

CONCAT(IFERROR(MID(A10,ROW($1:$26),1)*1,""))得到结果："1332344567"，乘以{0,1}得到数组{0,1332345677}，用第一参数0匹配这个数组中的0，第三参数为2，返回0对应的值1332344577。

以下《900套高逼格工作模板.xls 》免费下载，不收一分钱！

常用Excel

用Excel玩好报表

是必不可缺的技能

要知道一张好的图表

可以做到一图胜千言！

今天推荐的超实用干货是

《900套高逼格工作模板.xls 》

3.2G高逼格Excel可视化模板

制作精美 可直接套用

适合自用和内部培训使用

领取方式

关注我们

私信发送关键字：900

即可免费领取

资料来源于网络，公益分享，如有侵权，联系删除