excel学习库

一、数字在字符串的左边

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

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

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

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

此方法可以归纳为：在一个字符串当中提取一个字节的字符

3.VLOOKUP函数提取

=VLOOKUP(查找值,数据区域,列序数,[匹配方式])

当查找值不存在，会匹配到数据区域中最后一个与查找值类型相同且比查找值小的值它必须适用于字符串的最后面没有数字

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

依据VLOOKUP函数的特性，当查找值重复的时候，会匹配到第一个重复值即第一个0

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

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

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

6.LOOKUP函数提取数字

在查找向量中找不到时，会匹配到查找向量即数组中最后一个数值

7.LOOKUP函数提取数字

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

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。

--将文本型数值转化为数值型数据，非文本型数值都转化为错误值

二、数字在字符串的右边

1.RIGHT函数提取数字

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

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

必须保证字符串中只有一个1

2.VLOOKUP+RIGHT函数提取

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

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。

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。

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

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。原理上述已讲解，不再重复了。

2.FIND+MID提取数字

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

如果手机号码前面都带“电话”两个字，可以用公式：=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。

4.CTRL+E智能填充

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

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

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

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

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

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

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。

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

3.VLOOKUP函数

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

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

有些公式虽然看起来很复杂，但只要大家深刻理解这些函数的内涵，那么也就不难吃透它们点赞、关注和收藏