EXCEL VBA 使用正则表达式清洗替换数据

在EXCEL表内遇到有规律性的数据需要提取或者替换时，比如在数据中提取时间，身份证号码等，EXCEL内自带的查找替换就难以实现，我们可以利用VBA适用正则表达式去对数据进行匹配。

什么是正则表达式？

正则表达式，又称规则表达式。（英语：Regular Expression，在代码中常简写为regex、regexp或RE），计算机科学的一个概念。正则表达式通常被用来检索、替换那些符合某个模式(规则)的文本。

下表为正则表达式的元字符和对它们的一个简短的描述，新手可以百度下看下教程，不建议死记硬背，多做练习后更容易懂。

常用的正则表达式：

匹配手机号码：1[3|4|5|7|8][0-9]{9}

匹配身份证号（15位）：\d{14}[[0-9],0-9xX] ；身份证号（18位）：\d{17}(\d|X|x)

匹配邮箱地址：[\w!#$%&'*+/=?^_`{|}~-]+(?:\.[\w!#$%&'*+/=?^_`{|}~-]+)*@(?:[\w](?:[\w-]*[\w])?\.)+[\w](?:[\w-]*[\w])?

匹配(年-月-日)格式日期：([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8])))

匹配3位以上数字:[1-9][0-9]{2,}

匹配字符串之间的字符：前面的字符(.*?)后面的字符，比如abc123def ，需要匹配abc和def之间的字符的表达式就是abc(.*?)def

在VBA使用正则表达式代码如下：

Sub re()
Application.ScreenUpdating = False
Set oRegExp1 = CreateObject("vbscript.regexp")
re = InputBox("请输入正则表达式")
if re="" then exit sub
With oRegExp1
    .Global = True
    .IgnoreCase = True
    .Pattern = re
End With

For Each Rng In Selection
    if Rng <>"" then
        Set a = oRegExp1.Execute(Rng)
        For j = 0 To a.Count - 1
	    IF j=0 then
	        b = a(j)
	    else
       	        b = b & "," & a(j)
	    end if
        Next
        Rng.Value = b
        b = ""
    end if
Next
Application.ScreenUpdating = true
End Sub

例如在下面的产品名称内需要提取TM-xxx这个型号，则正则表达式为:TM-[0-9]{3,} （TM-为开头字符串加上3个数字以上）

选择需要提取的A列数据，运行VBA，在输入框内输入正则表达式，确认后就可以得到结果：

结果如下：

若有多个结果会以,号隔开放回结果。

另：正则表达式在线测试平台：http://tool.oschina.net/regex/