未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
Excel函数公式在杂乱文本中提取手机号,你会吗?

日常工作中,对数据的预处理往往花费很多的时候,本篇介绍从文本中提取出手机号的各种方法,建议收藏备用。

举例:A列是杂乱的文本数据,需要提取出手机号码,得到B列的结果:

find或search函数法

在B2单元格中输入公式:

=MID(A2,FIND('1',A2),11)

或者将FIND函数改成SEARCH,也是一样的效果:

=MID(A2,SEARCH('1',A2),11)

计算思路:

手机号码都是从1开始的,所以首先用FIND('1',A2),找到1所在的位置,然后用MID函数取11位得到手机号。

从这个思路也知道,这个是有BUG的,当前面的文本也出现了1的时候,该公式就不能使用了,如下所示:

MAX或min函数

在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求值的时候,要在原文本前面&一个任意的字符,如自学成才了吧。

lookup函数或VLOOKUP函数

在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方法

上述的函数公式方法都只能提取出一个手机号码,如果一个列中有两个手机号码的话,它是没有办法全部查找出来的,有的方法可能找到的是前面一个,有的方法是找到的后面一个

如果需要全部找出来的话,就可以使用VBA的方法来完成了,我们直接说制作过程:

❶按ALT F11,调出VBA编辑器,在空白处右键插入一个模块

然后在模块里面输入代码:

Function sz(xstr As String)Dim i As IntegerDim nFor i = 1 To Len(xstr)If Mid(xstr, i, 1) = 1 And IsNumeric(Mid(xstr, i, 11)) Thenn = Mid(xstr, i, 11)If Len(n) = 11 Thensz = sz & '/' & nEnd IfEnd IfNext iIf Len(sz) Thensz = Right(sz, Len(sz) - 1)Elsesz =''End IfEnd Function

这样就创建了一个自定义的函数sz()用来提取手机号,我们直接在公式里面输入

=sz(A2)

不管是几个手机号码,都可以快速的将它提取出来。

你学会了么,自己动手试试吧~

本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报
从APP上打开文章,阅读全文并永久保存 查看更多类似文章
来自:东西二王  > Excel
举报
[荐]  原创奖励计划来了,万元大奖等你拿!
猜你喜欢
类似文章
Excel也能提取手机号码?怎么回事?
Excel基础款:职场必备的20条Excel函数公式【附示例和解析】
Excel的一些使用技巧(2)
VLOOKUP()数组妙用,提取文本中的数字
为什么身份证号这么重要,Excel来告诉你!
Excel28:MOD,MID,VLOOKUP函数
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!