如何在文本中提取身份证号或手机号
发布日期:2015-03-18浏览:3770
某公司的一个业务系统会自动把若干列的数据合并在一起,从系统导出数据到EXCEL后,用户需要从中提取出相应的手机号码及身份证号码。
由于数据量比较大,且频繁需要此类操作。用户原计划准备人工一个个提取,但坚持一段时间后觉得工作量实在太大,于是找到微软OFFICE金牌讲师刘凌峰,希望能利用EXCEL的公式自动提取出来。
数据样表
工单反馈补充说明
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 不愿提供原因、不感兴趣
是否评级: 是 ; 评级客户名称: 叶X玮 ; 评级号码或证件号码: ******** ; 客户编码: ********0100 ; 不评级原因:
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 不愿提供原因、不感兴趣
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 不愿提供原因、不感兴趣
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 不愿提供原因、不感兴趣
2月已特评会员
是否评级: 是 ; 评级客户名称: 蔡X友 ; 评级号码或证件号码: ******** ; 客户编码: ********0000 ; 不评级原因:
是否评级: 否 ; 评级客户名称: 福州XX贸易有限公司 ; 评级号码或证件号码: ******** ; 客户编码: ********0000 ; 不评级原因: 用户拒绝
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 待考虑
是否评级: 是 ; 评级客户名称: 黄X娣 ; 评级号码或证件号码: ******** ; 客户编码: ********0000 ; 不评级原因:
是否评级: 是 ; 评级客户名称: 林X珍 ; 评级号码或证件号码: ******** ; 客户编码: ********0000 ; 不评级原因:
工单到期来不急呼三遍
是否评级: 是 ; 评级客户名称: 欧X萍 ; 评级号码或证件号码: ********151517 ; 客户编码: ********0000 ; 不评级原因:
是否评级: 是 ; 评级客户名称: 蔡X艳 ; 评级号码或证件号码: ********014 ; 客户编码: ********0000 ; 不评级原因:
3次无人接
是否评级: 是 ; 评级客户名称: 邓X斌 ; 评级号码或证件号码: ******** ; 客户编码: ********0000 ; 不评级原因:
是否评级: 是 ; 评级客户名称: 黄X彬 ; 评级号码或证件号码: ******** ; 客户编码: ********0000 ; 不评级原因:
数据分析
该份数据有些行有内容,有些行干脆没内容。有些行出现了手机号,有些行出现了身份证号,且出现的位置无规律。
因此,用简单的分列功能,无论是定长分列还是分隔符分列均无法满足用户的要求。只有通过函数或VBA程序来解决。
解决思路
1、 判断“评级号码或证件号码”是否有出现,以及出现的位置;如果没出现表示不可能有手机号或证件号码。
2、 判断“评级号码或证件号码”是否紧跟了号码。
3、 根据长度判断是否为手机号码。手机号均为11位。
4、 取出手机号码
5、 取出身份证号。考虑到18位身份证的普及,不再考虑有15位号码的身份证,否则还需增加判断。
涉及函数
条件判断:if()和iferror()
文本查找:find()
文本截取:mid()
文本代码:code()
逻辑函数:and()
分步函数
数据放在A列,为了使整个判断过程不至于太复杂,这里采用了分步判断的办法,也使各位看得更清楚。
工单反馈补充说明
号码位置
是否号码
是否手机号
手机号码
身份证号
判断号码位置:=IFERROR(FIND("码",A2,FIND("证",A2))+3,0)
判断是否号码:
=IFERROR(IF(AND(CODE(MID(A2,B2,1))>=CODE("1"),CODE(MID(A2,B2,1))<=CODE("9")),1,0),0)
判断是否手机号:
=IFERROR(IF(AND(CODE(MID(A2,B2+11,1))>=CODE("0"),CODE(MID(A2,B2+11,1))<=CODE("9")),0,1),0)
提取手机号:=IF(AND(C2=1,D2=1),MID(A2,B2,11),0)
提取身份证号:=IF(AND(C2=1,D2=0),MID(A2,B2,18),"")
结语
EXCEL的数据操作往往是会做,但工作量很大,许多办公人员均只会埋头苦干,不去寻求高效可靠的解决办公。如果你在办公中遇到类似的问题不妨与我们的金牌讲师联系,我们会提供一系列的解决方案给你。
如果希望将你的EXCEL从“单机版”变成“网络版”,一键生成报表,请联系我们。