某公司的一个业务系统会自动把若干列的数据合并在一起，从系统导出数据到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从“单机版”变成“网络版”，一键生成报表，请联系我们。