利用自定义函数获取混合内容单元格中的指定字符串

◆吴 争 刘 璐/谷城县审计局

由于原始数据录入不规范，经常会造成数据分析人员在前期数据结构化整理工作上花费较长的时间和较多的精力。近日，笔者在某审计项目中遇到此类情况，较多基础数据全部录入在一个单元格内，且没有较明显的规则来提取，因为需要身份证号码和手机号码等关键字段，所以必须要对基础数据开展清洗工作，转换成标准格式以满足审计需要。

部分数据（以下所有截屏数据均为演示数据）如图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正则表达式快速检索匹配字符串的功能，从混合内容且无规则的数据中检索提取所需数值（字符串）完美实现，既提高了工作效率，又保证了工作精度。