問題背景: 某公司的一個業務系統會自動把若幹列的數據合並在一起,從系統導出數據到EXCEL後,用戶需要從中提取出相應的手機號碼及身份證號碼。
由于數據量比較大,且頻繁需要此類操作。用戶原計劃准備人工一個個提取,但堅持一段時間後覺得工作量實在太大,于是找到我,希望能利用EXCEL的公式自動提取出來。
數據樣表工單反饋補充說明
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼: ; 客戶編碼: ; 不評級原因: 不願提供原因、不感興趣
是否評級: 是 ; 評級客戶名稱: 葉X玮 ; 評級號碼或證件號碼: 1330**16261 ; 客戶編碼: 2592168730700100 ; 不評級原因:
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼: ; 客戶編碼: ; 不評級原因: 不願提供原因、不感興趣
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼: ; 客戶編碼: ; 不評級原因: 不願提供原因、不感興趣
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼: ; 客戶編碼: ; 不評級原因: 不願提供原因、不感興趣
2月已特評會員
是否評級: 是 ; 評級客戶名稱: 蔡X友 ; 評級號碼或證件號碼: 1535**29799 ; 客戶編碼: 2592010127500000 ; 不評級原因:
是否評級: 否 ; 評級客戶名稱: 福州XX貿易有限公司 ; 評級號碼或證件號碼: 189**143231 ; 客戶編碼: 2592116572010000 ; 不評級原因: 用戶拒絕
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼: ; 客戶編碼: ; 不評級原因: 待考慮
是否評級: 是 ; 評級客戶名稱: 黃X娣 ; 評級號碼或證件號碼: 13358**8902 ; 客戶編碼: 2592036234590000 ; 不評級原因:
是否評級: 是 ; 評級客戶名稱: 林X珍 ; 評級號碼或證件號碼: 1805**15548 ; 客戶編碼: 2592450985620000 ; 不評級原因:
工單到期來不及呼三遍
是否評級: 是 ; 評級客戶名稱: 歐X萍 ; 評級號碼或證件號碼: 3501041983091**517 ; 客戶編碼: 2592451190660000 ; 不評級原因:
是否評級: 是 ; 評級客戶名稱: 蔡X豔 ; 評級號碼或證件號碼: 350221764***14 ; 客戶編碼: 2592102026510000 ; 不評級原因:
3次無人接
是否評級: 是 ; 評級客戶名稱: 鄧X斌 ; 評級號碼或證件號碼: 133957**023 ; 客戶編碼: 2592077043490000 ; 不評級原因:
是否評級: 是 ; 評級客戶名稱: 黃X彬 ; 評級號碼或證件號碼: 133930**635 ; 客戶編碼: 2592056620140000 ; 不評級原因:
數據分析該份數據有些行有內容,有些行幹脆沒內容。有些行出現了手機號,有些行出現了身份證號,且出現的位置無規律。
因此,用簡單的分列功能,無論是定長分列還是分隔符分列均無法滿足用戶的要求。只有通過函數或VBA程序來解決。
解決思路判斷“評級號碼或證件號碼”是否有出現,以及出現的位置;如果沒出現表示不可能有手機號或證件號碼。判斷“評級號碼或證件號碼”是否緊跟了號碼。根據長度判斷是否爲手機號碼。手機號均爲11位。取出手機號碼取出身份證號。考慮到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))