某公司的一个业务系统会自动把若干列的数据合并在一起，从系统导出数据到EXCEL后，用户需要从中提取出相应的手机号码及身份证号码。
由于数据量比较大，且频繁需要此类操作。用户原计划准备人工一个个提取，但坚持一段时间后觉得工作量实在太大，于是找到微软OFFICE金牌讲师刘凌峰，希望能利用EXCEL的公式自动提取出来。
工单反馈补充说明 |
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码： ; 客户编码： ; 不评级原因: 不愿提供原因、不感兴趣 |
是否评级: 是 ； 评级客户名称: 叶X玮 ； 评级号码或证件号码： 13306216261 ； 客户编码： 2592168730700100 ； 不评级原因: |
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码： ; 客户编码： ; 不评级原因: 不愿提供原因、不感兴趣 |
|
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码： ; 客户编码： ; 不评级原因: 不愿提供原因、不感兴趣 |
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码： ; 客户编码： ; 不评级原因: 不愿提供原因、不感兴趣 |
2月已特评会员 |
是否评级: 是 ; 评级客户名称: 蔡X友 ; 评级号码或证件号码： 15359229799 ; 客户编码： 2592010127500000 ; 不评级原因: |
是否评级: 否 ; 评级客户名称: 福州XX贸易有限公司 ; 评级号码或证件号码： 18965143231 ; 客户编码： 2592116572010000 ; 不评级原因: 用户拒绝 |
是否评级: 否 ; 评级客户名称: ; 评级号码或证件号码： ; 客户编码： ; 不评级原因: 待考虑 |
是否评级: 是 ; 评级客户名称: 黄X娣 ; 评级号码或证件号码： 13358388902 ; 客户编码： 2592036234590000 ; 不评级原因: |
是否评级: 是 ; 评级客户名称: 林X珍 ; 评级号码或证件号码： 18051015548 ; 客户编码： 2592450985620000 ; 不评级原因: |
工单到期来不急呼三遍 |
|
是否评级: 是 ; 评级客户名称: 欧X萍 ; 评级号码或证件号码： 350104198309151517 ; 客户编码： 2592451190660000 ; 不评级原因: |
是否评级: 是 ; 评级客户名称: 蔡X艳 ; 评级号码或证件号码： 350221764404014 ; 客户编码： 2592102026510000 ; 不评级原因: |
3次无人接 |
|
是否评级: 是 ; 评级客户名称: 邓X斌 ; 评级号码或证件号码： 13395713023 ; 客户编码： 2592077043490000 ; 不评级原因: |
是否评级: 是 ; 评级客户名称: 黄X彬 ; 评级号码或证件号码： 13393050635 ; 客户编码： 2592056620140000 ; 不评级原因: |
该份数据有些行有内容，有些行干脆没内容。有些行出现了手机号，有些行出现了身份证号，且出现的位置无规律。
因此，用简单的分列功能，无论是定长分列还是分隔符分列均无法满足用户的要求。只有通过函数或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),"")
浙公网安备 33010802003509号
杭州讲师网络科技有限公司
