劉凌峰,劉凌峰講師,劉凌峰聯系方式,劉凌峰培訓師-【中華講師網】
微軟OFFICE金牌講師
44
鮮花排名
0
鮮花數量
掃一掃加我微信
劉凌峰:金牌EXCEL老師劉凌峰教你如何在文本中提取身份證號或手機號
2016-01-20 56051

問題背景:

  某公司的一個業(yè)務系統(tǒng)會自動把若干列的數據合并在一起,從系統(tǒng)導出數據到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 ; 不評級原因:

 

數據分析

該份數據有些行有內容,有些行干脆沒內容。有些行出現了手機號,有些行出現了身份證號,且出現的位置無規(guī)律。

因此,用簡單的分列功能,無論是定長分列還是分隔符分列均無法滿足用戶的要求。只有通過函數或VBA程序來解決。

解決思路

1、  判斷“評級號碼或證件號碼”是否有出現,以及出現的位置;如果沒出現表示不可能有手機號或證件號碼。

2、  判斷“評級號碼或證件號碼”是否緊跟了號碼。

3、  根據長度判斷是否為手機號碼。手機號均為11位。

4、  取出手機號碼

5、  取出身份證號??紤]到18位身份證的普及,不再考慮有15位號碼的身份證,否則還需增加判斷。

涉及函數

條件判斷:if()iferror()

文本查找:find()

文本截?。?/span>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),"")

 

全部評論 (0)

Copyright©2008-2025 版權所有 浙ICP備06026258號-1 浙公網安備 33010802003509號 杭州講師網絡科技有限公司
講師網 kasajewelry.com 直接對接10000多名優(yōu)秀講師-省時省力省錢
講師網常年法律顧問:浙江麥迪律師事務所 梁俊景律師 李小平律師