日常工作中,对数据的预处理往往花费很多的时候,本篇介绍从文本中提取出手机号的各种方法,建议收藏备用。
举例:A列是杂乱的文本数据,需要提取出手机号码,得到B列的结果:
在B2单元格中输入公式:
=MID(A2,FIND(“1”,A2),11)
或者将FIND函数改成SEARCH,也是一样的效果:
=MID(A2,SEARCH(“1”,A2),11)
计算思路:
手机号码都是从1开始的,所以首先用FIND(“1”,A2),找到1所在的位置,然后用MID函数取11位得到手机号。
从这个思路也知道,这个是有BUG的,当前面的文本也出现了1的时候,该公式就不能使用了,如下所示:
在B2中输入的公式是:
=MIN(IFERROR(–MID(A2&”自学成才”,ROW($1:$100),11),”Excel”))
然后按CTRL+SHIFT+ENTER三键 计算
或者输入的公式是:
=MAX(IFERROR(–MID(A2,ROW($1:$100),11),0))
按三键计算
计算思路
❶用到数组从左至右一直提取11位字符,
❷然后用–号来进行两次负数计算,是正常的数字的话,就会得到数字,如果有文本的话,就会出错,
❸然后用IFERROR来屏蔽错误值
❹然后取最大值,或最小值来得到结果
下图是MAX函数一步步拆分的过程
所以知道为什么用MIN求值的时候,要在原文本前面&一个任意的字符,如自学成才了吧。
在B2中输入公式:
=-LOOKUP(,-MID(A2&”a”,ROW($1:$100),11))
或者使用VLOOKUP函数公式:
=VLOOKUP(,MID(A2,ROW($1:$100),11){0,1},2,)
输入完公式按CTLR+SHIFT+enter三键计算
计算思路:
❶每隔11位拆分文本里面的字符
❷lookup函数构建了一列数据,vlookup函数构建了2列匹配的数据
❸模糊查找匹配得到结果
大家可以根据思路同样的画出计算过程。
上述的函数公式方法都只能提取出一个手机号码,如果一个列中有两个手机号码的话,它是没有办法全部查找出来的,有的方法可能找到的是前面一个,有的方法是找到的后面一个
如果需要全部找出来的话,就可以使用VBA的方法来完成了,我们直接说制作过程:
❶按ALT+F11,调出VBA编辑器,在空白处右键插入一个模块
然后在模块里面输入代码:
“`
这样就建立了一个名为sz()的自定义函数来提取手机号码,我们只需在单元格输入以下公式即可:
=sz(A2)
无论有多少个手机号码,都能快速提取出来。
学会了吗?赶快试试吧~