利用自定義函數獲取混合內容單元格中的指定字符串

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

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

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

圖1

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

一、利用VLOOKUP函數探索

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

實際運算后發現函數提取超過11位顯示為科學計數，如圖2所示。

圖2

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

圖3

觀察發現，計算結果與實際不符。看來利用VLOOK?UP函數加入數組計算提取18位的身份證號碼行不通，只能另辟蹊徑。

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

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

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

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

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

Dim Reg

Dim Mhs

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

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'如果自定義函數的第二參數小于0或者大于匹配字符串組數就繼續執行

GetCardID="#VALUE"'結果顯示"#VALUE"錯誤值

Exit Function'退出過程

End If

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

End Function

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

三、最終實現既定目標

設置要輸出身份證號碼的單元格D2=GetCardID(C2，1)，往下填充至需要計算的行，得到所需準確結果，如圖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

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

圖5

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