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