某公司的一個業務系統會自動把若干列的數據合并在一起，從系統導出數據到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號
杭州講師網絡科技有限公司
