利用自定義函數(shù)獲取混合內(nèi)容單元格中的指定字符串

◆吳 爭 劉 璐/谷城縣審計局

由于原始數(shù)據(jù)錄入不規(guī)范，經(jīng)常會造成數(shù)據(jù)分析人員在前期數(shù)據(jù)結(jié)構(gòu)化整理工作上花費較長的時間和較多的精力。近日，筆者在某審計項目中遇到此類情況，較多基礎(chǔ)數(shù)據(jù)全部錄入在一個單元格內(nèi)，且沒有較明顯的規(guī)則來提取，因為需要身份證號碼和手機號碼等關(guān)鍵字段，所以必須要對基礎(chǔ)數(shù)據(jù)開展清洗工作，轉(zhuǎn)換成標準格式以滿足審計需要。

部分數(shù)據(jù)（以下所有截屏數(shù)據(jù)均為演示數(shù)據(jù)）如圖1所示。

圖1

從圖中可以看到，C列單元格中包含了人員的社區(qū)信息、身份證號碼、性別、手機號碼、戶籍屬性。

一、利用VLOOKUP函數(shù)探索

起初考慮用VLOOKUP函數(shù)加入數(shù)組計算方式來解決，設(shè)置要輸出身份證號碼的單元格D2=VLOOKUP(0，MID(C2，ROW($1:$99)，18)*{0，1}，2，0)。思路是 MID 函數(shù)依次從C2的第1、2、3、4……直至99個位置，提取長度為18位的字符，然后分別乘以0和1，即常量數(shù)組{0，1}。如果MID函數(shù)的結(jié)果為文本，那么乘以{0，1}后結(jié)果為錯誤值{#VALUE!，#VALUE!}；如果MID函數(shù)的結(jié)果為數(shù)值，結(jié)果即為所需提取的18位身份證號碼。

實際運算后發(fā)現(xiàn)函數(shù)提取超過11位顯示為科學(xué)計數(shù)，如圖2所示。

圖2

于是考慮用英文引號拼接函數(shù)來調(diào)整顯示格式，修改單 元 格 D2="'"&VLOOKUP(0，MID(C3，ROW($1:$99)，18)*{0，1}，2，0)，運行結(jié)果如圖3。

圖3

觀察發(fā)現(xiàn)，計算結(jié)果與實際不符?？磥砝肰LOOK?UP函數(shù)加入數(shù)組計算提取18位的身份證號碼行不通，只能另辟蹊徑。

二、運用VBA正則運算解決問題

VBA正則表達式是一種特殊的字符串模式，用于匹配字符串排列的一套規(guī)則。我們可以用這個規(guī)則去匹配查找可以匹配上的字符串（即單元格中任意想要的信息）。簡單來說，就是單元格中存在一個文本信息，這個信息中有一些我們需要的內(nèi)容，也有很多不需要的內(nèi)容，通過正則表達式幫助我們從文本中提取想要的內(nèi)容。

如上例中因為身份證號碼出現(xiàn)位置不固定，我們無法使用函數(shù)LEFT或者MID或者RIGHT來獲取身份證號碼，使用正則表達式可以快速獲取身份證號碼。

在表格中按下Alt+F11進入設(shè)計模式，插入模塊，編寫語句后保存。語句及注釋如下：

Function GetCardID(rng As Range，i As Integer)'格式=GetCardID(A1，1)，A1代表單元格，1代表從里面提取第幾組，必須在字符之間，但加"|$"可以在字符之后

Dim Reg

Dim Mhs

Set Reg=CreateObject("vbscript.regexp")'創(chuàng)建正則表達式對象

With Reg

.Pattern="(?:^|D)(d{18}|d{15})(?=^|D)"'匹配正則表達式，加"|$"可以提取在字符串之后

Dim Reg

.Global=True

Set Mhs=Reg.Execute(rng.Value)'將所有匹配的字符串賦值給Mhs

End With

If i-1＜0 Or i＞Mhs.Count Then'如果自定義函數(shù)的第二參數(shù)小于0或者大于匹配字符串組數(shù)就繼續(xù)執(zhí)行

GetCardID="#VALUE"'結(jié)果顯示"#VALUE"錯誤值

Exit Function'退出過程

End If

GetCardID=Mhs(i-1).submatches(0)'結(jié)果等于匹配字符串的指定組數(shù)

End Function

其中With語句塊作用是匹配15位或18位的字符串賦值給Mhs，IF語句塊作用是判斷自定義函數(shù)的第二參數(shù)是否在計算范圍，兩個語句塊之后就是從0開始進行匹配計算，成功匹配的字符串賦值給GetCardID函數(shù)。

三、最終實現(xiàn)既定目標

設(shè)置要輸出身份證號碼的單元格D2=GetCardID(C2，1)，往下填充至需要計算的行，得到所需準確結(jié)果，如圖4所示。

圖4

同樣的思路編寫提取手機號碼的語句：

Function GetPhoneNumber(rng As Range，i As Integer)

Dim Reg

Dim Mhs

Set Reg=CreateObject("vbscript.regexp")

With Reg

.Pattern="(?:^|D)(d{11})(?=D|$)"

.Global=True

Set Mhs=Reg.Execute(rng.Value)

End With

If i-1＜0 Or i＞Mhs.Count Then

GetPhoneNumber="#VALUE"

Exit Function

End If

GetPhoneNumber=Mhs(i-1).submatches(0)

End Function

設(shè)置要輸出手機號碼的單元格E2=GetPhoneNumber(C2，1)，往下填充至需要計算的行，得到所需準確結(jié)果，如圖5所示。

圖5

至此，利用VBA正則表達式快速檢索匹配字符串的功能，從混合內(nèi)容且無規(guī)則的數(shù)據(jù)中檢索提取所需數(shù)值（字符串）完美實現(xiàn)，既提高了工作效率，又保證了工作精度。