专业微软培训机构,为北京、上海、广州、深圳、长沙、武汉、福州、厦门、泉州、漳州等城市提供EXCEL、PPT、WORD、ACCESS、PROJECT、MCSE、MCITP、OFFICE等高级培训及相关技术服务。

0592-2962798 2963798
2966798 2968798
厦门市厦禾路南洋大厦二楼

最新文章列表

高士达平台设计开发的泰欣订单管理系..

EXCEL一键实现报关单据生成

[EXCEL]高手都是这么写公式的

快速将EXCEL表中的内容缩放至一页进..

建发汽车干部考核系统实现方案

金牌OFFICE老师刘凌峰教你利用VBA调..

热门文章列表

如何解决EXCEL运行速度慢的问题

利用EXCEL VBA向WORD生成表格和图表..

【高士达下载】高士达工具箱

瑞声达模具管理系统顺利升级到WINDO..

【课程】深入挖掘EXCEL函数宝藏

利用VLOOKUP提取多个条件的同一结果..

金牌EXCEL老师刘凌峰教你如何在文本中提取身份证号或手机号

关键字:EXCEL技巧    发布时间:2015-04-10 14:15:07    作者:Excel培训管理员

问题背景:

     某公司的一个业务系统会自动把若干列的数据合并在一起,从系统导出数据到EXCEL后,用户需要从中提取出相应的手机号码及身份证号码。

由于数据量比较大,且频繁需要此类操作。用户原计划准备人工一个个提取,但坚持一段时间后觉得工作量实在太大,于是找到微软OFFICE金牌讲师刘凌峰,希望能利用EXCEL的公式自动提取出来。

数据样表

工单反馈补充说明

是否评级: ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 不愿提供原因、不感兴趣

是否评级: 评级客户名称: 叶X玮 评级号码或证件号码: 13306216261 客户编码: 2592168730700100 不评级原因:

是否评级: ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 不愿提供原因、不感兴趣

是否评级: ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 不愿提供原因、不感兴趣

是否评级: ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 不愿提供原因、不感兴趣

2月已特评会员

是否评级: ; 评级客户名称: 蔡X友 ; 评级号码或证件号码: 15359229799 ; 客户编码: 2592010127500000 ; 不评级原因:

是否评级: ; 评级客户名称: 福州XXX贸易有限公司 ; 评级号码或证件号码: 18965143231 ; 客户编码: 2592116572010000 ; 不评级原因: 用户拒绝

是否评级: ; 评级客户名称: ; 评级号码或证件号码: ; 客户编码: ; 不评级原因: 待考虑

是否评级: ; 评级客户名称: 黄X娣 ; 评级号码或证件号码: 13358388902 ; 客户编码: 2592036234590000 ; 不评级原因:

是否评级: ; 评级客户名称: 林X珍 ; 评级号码或证件号码: 18051015548 ; 客户编码: 2592450985620000 ; 不评级原因:

工单到期来不急呼三遍

是否评级: ; 评级客户名称: 欧X萍 ; 评级号码或证件号码: 350104198309151517 ; 客户编码: 2592451190660000 ; 不评级原因:

是否评级: ; 评级客户名称: 蔡X艳 ; 评级号码或证件号码: 350221764404014 ; 客户编码: 2592102026510000 ; 不评级原因:

3次无人接

是否评级: ; 评级客户名称: 邓X斌 ; 评级号码或证件号码: 13335713023 ; 客户编码: 2592077043491000 ; 不评级原因:

是否评级: ; 评级客户名称: 黄X彬 ; 评级号码或证件号码: 13395050635 ; 客户编码: 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),"")

各位以后遇到EXCEL难题或需要EXCEL方面的培训,不妨联系我们。

本文关键词:EXCEL技巧   

厦门高士达是一家微软认证培训机构,是专业的EXCEL培训、PPT培训、ACCESS培训、OFFICE培训、微软培训、微软考试及其他IT服务供应商 ©2014 厦门高士达微软高级技术教育中心   闽ICP备14021819号 技术支持:港湾有巢

闽公网安备 35020302001653号