某公司的一個業(yè)務(wù)系統(tǒng)會自動把若干列的數(shù)據(jù)合并在一起，從系統(tǒng)導(dǎo)出數(shù)據(jù)到EXCEL后，用戶需要從中提取出相應(yīng)的手機號碼及身份證號碼。
由于數(shù)據(jù)量比較大，且頻繁需要此類操作。用戶原計劃準(zhǔn)備人工一個個提取，但堅持一段時間后覺得工作量實在太大，于是找到微軟OFFICE金牌講師劉凌峰，希望能利用EXCEL的公式自動提取出來。
工單反饋補充說明 |
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼： ; 客戶編碼： ; 不評級原因: 不愿提供原因、不感興趣 |
是否評級: 是 ； 評級客戶名稱: 葉X瑋 ； 評級號碼或證件號碼： 13306216261 ； 客戶編碼： 2592168730700100 ； 不評級原因: |
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼： ; 客戶編碼： ; 不評級原因: 不愿提供原因、不感興趣 |
|
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼： ; 客戶編碼： ; 不評級原因: 不愿提供原因、不感興趣 |
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼： ; 客戶編碼： ; 不評級原因: 不愿提供原因、不感興趣 |
2月已特評會員 |
是否評級: 是 ; 評級客戶名稱: 蔡X友 ; 評級號碼或證件號碼： 15359229799 ; 客戶編碼： 2592010127500000 ; 不評級原因: |
是否評級: 否 ; 評級客戶名稱: 福州XX貿(mào)易有限公司 ; 評級號碼或證件號碼： 18965143231 ; 客戶編碼： 2592116572010000 ; 不評級原因: 用戶拒絕 |
是否評級: 否 ; 評級客戶名稱: ; 評級號碼或證件號碼： ; 客戶編碼： ; 不評級原因: 待考慮 |
是否評級: 是 ; 評級客戶名稱: 黃X娣 ; 評級號碼或證件號碼： 13358388902 ; 客戶編碼： 2592036234590000 ; 不評級原因: |
是否評級: 是 ; 評級客戶名稱: 林X珍 ; 評級號碼或證件號碼： 18051015548 ; 客戶編碼： 2592450985620000 ; 不評級原因: |
工單到期來不急呼三遍 |
|
是否評級: 是 ; 評級客戶名稱: 歐X萍 ; 評級號碼或證件號碼： 350104198309151517 ; 客戶編碼： 2592451190660000 ; 不評級原因: |
是否評級: 是 ; 評級客戶名稱: 蔡X艷 ; 評級號碼或證件號碼： 350221764404014 ; 客戶編碼： 2592102026510000 ; 不評級原因: |
3次無人接 |
|
是否評級: 是 ; 評級客戶名稱: 鄧X斌 ; 評級號碼或證件號碼： 13395713023 ; 客戶編碼： 2592077043490000 ; 不評級原因: |
是否評級: 是 ; 評級客戶名稱: 黃X彬 ; 評級號碼或證件號碼： 13393050635 ; 客戶編碼： 2592056620140000 ; 不評級原因: |
該份數(shù)據(jù)有些行有內(nèi)容，有些行干脆沒內(nèi)容。有些行出現(xiàn)了手機號，有些行出現(xiàn)了身份證號，且出現(xiàn)的位置無規(guī)律。
因此，用簡單的分列功能，無論是定長分列還是分隔符分列均無法滿足用戶的要求。只有通過函數(shù)或VBA程序來解決。
1、 判斷“評級號碼或證件號碼”是否有出現(xiàn)，以及出現(xiàn)的位置；如果沒出現(xiàn)表示不可能有手機號或證件號碼。
2、 判斷“評級號碼或證件號碼”是否緊跟了號碼。
3、 根據(jù)長度判斷是否為手機號碼。手機號均為11位。
4、 取出手機號碼
5、 取出身份證號。考慮到18位身份證的普及，不再考慮有15位號碼的身份證，否則還需增加判斷。
條件判斷：if()和iferror()
文本查找：find()
文本截取：mid()
文本代碼：code()
邏輯函數(shù)：and()
數(shù)據(jù)放在A列，為了使整個判斷過程不至于太復(fù)雜，這里采用了分步判斷的辦法，也使各位看得更清楚。
工單反饋補充說明 | 號碼位置 | 是否號碼 | 是否手機號 | 手機號碼 | 身份證號 |
判斷號碼位置：=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),"")
講師網(wǎng)官微
找講師小程序
浙公網(wǎng)安備 33010802003509號
杭州講師網(wǎng)絡(luò)科技有限公司
