EXCEL VBA 正则表达式 从实例开始.doc

EXCEL VBA 正则表达式 从实例开始所有代码都测试过2011-09-09：/dxy1982/archive/2011/09/09/2159246.html2010-08-08：/thread-137472-1-1.html数据验证经常出现的情况是，需要验证和处理大量的文本内容，有时候需要查找字符串，有时候要按条件替换，并且这些待处理的问题还是有一定的规律可循的，但是无法通过枚举的方式挨个验证，这个时候就需要正则表达式来帮忙。1.正则表达式(Regular Expression)基础正则表达式是通用的文本搜索和处理方案，它的知识不是VBA独有的，基本上每种语言都内置了正则表达式的功能。正则表达式的基础知识不是这里的重点，需要的朋友可以Google一下，或者参看下面的一些入门教程：/tutorials/regex/regex.htm/zh/regref.htm/archives/433.html2.VBA中的正则表达式应用在VBA中使用正则表达式，可以通过下列途径实现：创建正则表达式对象:前期绑定：在VBA代码编辑器中的Tools菜单中，选中References.，然后引用Microsoft VBScript Regular Expressions 5.5类库，然后直接定义对象：Dim reg As New RegExp。后期绑定：使用CreateObject方法定义对象：CreateObject(VBSCRIPT.REGEXP)。前一种方式的优点是可以有编辑器的Intellisense支持?RegExp对象的属性:Global - 设置或返回一个 Boolean 值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。如果搜索应用于整个字符串，Global 属性的值应该为 True，否则其值为 False。默认的设置为True。Multiline - 返回正则表达式是否具有标志m, 缺省值为False。如果指定的搜索字符串分布在多行，这个属性是要设置为True的。IgnoreCase - 设置或返回一个Boolean值，指明模式搜索是否区分大小写。如果搜索是区分大小写的，则 IgnoreCase 属性应该为False；否则应该设为True。缺省值为True。Pattern - 设置或返回被搜索的正则表达式模式。 被搜索的正则字符串表达式。它包含各种正则表达式字符。RegExp对象的方法:Execute - 对指定的字符串执行正则表达式搜索。需要传入要在其上执行正则表达式的文本字符串。正则表达式搜索的设计模式是通过 RegExp对象的Pattern来设置的。Execute方法返回一个Matches集合，其中包含了在string中找到的每一个匹配的Match对 象。如果未找到匹配，Execute将返回空的Matches集合。Replace -替换在正则表达式查找中找到的文本?Test - 对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。RegExp.Global属性对Test方法没有影响。如果找到了匹配的模式，Test方法返回True；否则返回False。MatchCollection对象与Match对象匹配到的所有对象放在MatchCollection集合中，这个集合对象只有两个只读属性：Count: 匹配到的对象的数目Item：集合的又一通用方法，需要传入Index值获取指定的元素。一般，可以使用For Each语句枚举集合中的对象。集合中对象的类型是Match。Match对象有以下几个只读的属性:FirstIndex - 匹配字符串在整个字符串中的位置，值从0开始。Length -匹配字符串的长度?Value -匹配的字符串?SubMatches - 集合，匹配字符串中每个分组的值。作为集合类型，有Count和Item两个属性。常用的正则表达式主要有以下几种:匹配中文字符的正则表达式: u4e00-u9fa5评注：匹配中文还真是个头疼的事，有了这个表达式就好办了匹配双字节字符 (包括汉字在内): x00-xff评注：可以用来计算字符串的长度（一个双字节字符长度计2，ASCII字符计1）匹配空白行的正则表达式：ns*r评注: 可以用来删除空白行匹配HTML标记的正则表达式：*.*?|评注：网上流传的版本太糟糕，上面这个也仅仅能匹配部分，对于复杂的嵌套标记依旧无能为力匹配首尾空白字符的正则表达式：s*|s*$评注：可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式匹配Email地址的正则表达式：w+(-+.w+)*w+(-.w+)*.w+(-.w+)*评注: 表单验证时很实用匹配网址URL的正则表达式：a-zA-z+:/s*评注：网上流传的版本功能很有限，上面这个基本可以满足需求匹配帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：a-zA-Za-zA-Z0-9_4,15$评注: 表单验证时很实用匹配国内电话号码：d-d|d-d评注：匹配形式如或配腾讯QQ号：1-90-94,评注: 腾讯QQ号从10000开始匹配中国邮政编码：1-9d(?!d)评注: 中国邮政编码为6位数字匹配身份证：d|d评注: 中国的身份证为15位或18位匹配ip地址：d+.d+.d+.d+评注: 提取ip地址时有用匹配特定数字:1-9d*$/匹配正整数-1-9d*$ /匹配负整数-?1-9d*$/匹配整数1-9d*|0$/匹配非负整数（正整数 + 0）-1-9d*|0$/匹配非正整数（负整数 + 0）1-9d*.d*|0.d*1-9d*$/匹配正浮点数-(1-9d*.d*|0.d*1-9d*)$/匹配负浮点数-?(1-9d*.d*|0.d*1-9d*|0?.0+|0)$/匹配浮点数1-9d*.d*|0.d*1-9d*|0?.0+|0$/匹配非负浮点数（正浮点数 + 0）(-(1-9d*.d*|0.d*1-9d*)|0?.0+|0$/匹配非正浮点数（负浮点数 + 0）评注：处理大量数据时有用，具体应用时注意修正匹配特定字符串:A-Za-z+$/匹配由26个英文字母组成的字符串A-Z+$/匹配由26个英文字母的大写组成的字符串a-z+$/匹配由26个英文字母的小写组成的字符串A-Za-z0-9+$/匹配由数字和26个英文字母组成的字符串w+$/匹配由数字、26个英文字母或者下划线组成的字符串评注: 最基本也是最常用的一些表达式代码/语法 说明 匹配除换行符以外的任意字符 .w 匹配字母或数字或下划线或汉字s 匹配任意的空白符d 匹配数字b 匹配单词的开始或结束 匹配字符串的开始$ 匹配字符串的结束表2.常用的限定符代码/语法 说明* 重复零次或更多次 重复一次或更多次 +Print 重复零次或一次n 重复n次n, 重复n次或更多次n,m 重复n到m次表3.常用的反义代码代码/语法 说明W 匹配任意不是字母，数字，下划线，汉字的字符S 匹配任意不是空白符的字符D 匹配任意非数字的字符B 匹配不是单词开头或结束的位置x 匹配除了x以外的任意字符aeiou 匹配除了aeiou这几个字母以外的任意字符表4.常用分组语法代码/语法 说明 分类(exp) 匹配exp,并捕获文本到自动命名的组里 捕获(?exp) 匹配exp,并捕获文本到名称为name的组里，也可以写成(?nameexp)(?:exp) 匹配exp,不捕获匹配的文本，也不给此分组分配组号(?=exp) 匹配exp前面的位置 零宽断言(?=exp) 匹配exp后面的位置(?!exp) 匹配后面跟的不是exp的位置(?exp) 贪婪子表达式(?-exp) 平衡组(?im-nsx:exp) 在子表达式exp中改变处理选项(?im-nsx) 为表达式后面的部分改变处理选项(?(exp)yes|no) 把exp当作零宽正向先行断言，如果在这个位置能匹配，使用yes作为此组的表达式；否则使用no(?(exp)yes) 同上，只是使用空表达式作为no(?(name)yes|no) 如果命名为name的组捕获到了内容，使用yes作为表达式；否则使用no(?(name)yes) 同上，只是使用空表达式作为no在VBA中使用正则表达式，可以通过下列途径实现：创建正则表达式对象:前期绑定：在VBA代码编辑器中的Tools菜单中，选中References.，然后引用Microsoft VBScript Regular Expressions 5.5类库，然后直接定义对象：Dim reg As New RegExp。后期绑定：使用CreateObject方法定义对象：CreateObject(VBSCRIPT.REGEXP)。前一种方式的优点是可以有编辑器的Intellisense支持?RegExp对象的属性:Global - 设置或返回一个 Boolean 值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。如果搜索应用于整个字符串，Global 属性的值应该为 True，否则其值为 False。默认的设置为True。Multiline - 返回正则表达式是否具有标志m, 缺省值为False。如果指定的搜索字符串分布在多行，这个属性是要设置为True的。IgnoreCase - 设置或返回一个Boolean值，指明模式搜索是否区分大小写。如果搜索是区分大小写的，则 IgnoreCase 属性应该为False；否则应该设为True。缺省值为True。Pattern - 设置或返回被搜索的正则表达式模式。 被搜索的正则字符串表达式。它包含各种正则表达式字符。RegExp对象的方法:Execute - 对指定的字符串执行正则表达式搜索。需要传入要在其上执行正则表达式的文本字符串。正则表达式搜索的设计模式是通过 RegExp对象的Pattern来设置的。Execute方法返回一个Matches集合，其中包含了在string中找到的每一个匹配的Match对象。如果未找到匹配，Execute将返回空的Matches集合。Replace -替换在正则表达式查找中找到的文本?Test - 对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。RegExp.Global属性对Test方法没有影响。如果找到了匹配的模式，Test方法返回True；否则返回False。MatchCollection对象与Match对象匹配到的所有对象放在MatchCollection集合中，这个集合对象只有两个只读属性：Count: 匹配到的对象的数目Item：集合的又一通用方法，需要传入Index值获取指定的元素。一般，可以使用For Each语句枚举集合中的对象。集合中对象的类型是Match。Match对象有以下几个只读的属性:FirstIndex - 匹配字符串在整个字符串中的位置，值从0开始。Length -匹配字符串的长度?Value -匹配的字符串?SubMatches - 集合，匹配字符串中每个分组的值。作为集合类型，有Count和Item两个属性。正则表达式很强大，是处理文本的首选，不管你信不信，反正我是信了。说了很多，最后以一个小例子结尾吧: Sub Test() Dim reg As New RegExp With reg .Global = True .IgnoreCase = True .Pattern = d+ End With Dim mc As MatchCollection Dim m As match Set mc = reg.Execute(123aaaaa987uiiui999) For Each m In mc MsgBox m.Value NextEnd SubFunction ExReplace(sStr As String, sReplStr As String, sPatrn As String) As String正则表达式替换参数说明：sStr原字符串，sReplStr将替换的字符串，sPatrn样式返回参数，返回替换后的值 Dim regEX As Object Set regEX = CreateObject(VBSCRIPT.REGEXP) RegEx为建立正则表达式 regEX.Global = True 设置全局可用 regEX.Pattern = sPatrn 设置样式 ExReplace = regEX.Replace(sStr, sReplStr) 执行替换 Set regEX = NothingEnd FunctionFunction ExExce(sStr As String, sPatrn As String, Optional IC As Boolean = True, Optional G As Boolean = True) As Object正则表达式搜索参数说明：sStr原字符串，Patrn样式,IC是否区别大小写,G是否全局可用返回参数，返回的是一个对象，ExExce.Count是搜索的数量 ExExce(n).FirstIndex搜索的第n个串的位置，n=0 ExExce(n).Value搜索的第n个串的值，n=0 Dim regEX As Object Set regEX = CreateObject(VBSCRIPT.REGEXP) RegEx为建立正则表达式 regEX.Global = True 设置全局可用 regEX.Pattern = sPatrn 设置样式 regEX.IgnoreCase = IC 设置是否区分大小写。 Set ExExce = regEX.Execute(sStr) 执行搜索 Set regEX = NothingEnd FunctionFunction ExTest(sStr As String, sPatrn As String, IC As Boolean) As Boolean正则表达式匹配VBA代码参数说明：sStr原字符串，Patrn样式,IC是否区别大小写返回参数，返回一个逻辑值，True为匹配，False为不匹配 Dim regEX As Object Set regEX = CreateObject(VBSCRIPT.REGEXP) RegEx为建立正则表达式 regEX.Global = True 设置全局可用 regEX.Pattern = sPatrn 设置样式 regEX.IgnoreCase = IC 设置是否区分大小写。 ExTest = regEX.Test(sStr) 执行搜索测试 Set regEX = NothingEnd FunctionPublic Sub 去重复() Dim ss, re, rv ss = Is is the cost of of gasoline going up up?. & vbNewLine Set re = New RegExp re.Pattern = b(a-z+) 1b re.Global = True re.IgnoreCase = True re.MultiLine = True rv = re.Replace(ss, $1) MsgBox rvEnd SubFunction RegExpTest(patrn, strng)正则表达式(RegExp)对象提供简单的正则表达式支持功能?下面的代码说明了RegExp对象的用法: Dim regEX, match, matches 建立变量。 Set regEX = New RegExp 建立正则表达式。 regEX.Pattern = patrn 设置模式。 regEX.IgnoreCase = True 设置是否区分字符大小写。 regEX.Global = True 设置全局可用性。 Set matches = regEX.Execute(strng) 执行搜索。 For Each match In matches 遍历匹配集合。 retStr = retStr & Match found at position retStr = retStr & match.FirstIndex & . Match Value is retStr = retStr & match.Value & . & vbCrLf Next RegExpTest = retStrEnd FunctionGlobal 属性设置或返回一个 Boolean 值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。Object.Global = True | False 对象 参数总是 RegExp 对象。如果搜索应用于整个字符串，Global 属性的值为 True，否则其值为 False。默认的设置为 False。下面的代码说明了 Global 属性的用法（改变赋予 Global 属性的值并观察其效果）：Function RegExpTest1(patrn, strng) Dim regEX, match, matches 建立变量。 Set regEX = New RegExp 建立规范表达式。 regEX.Pattern = patrn 设置模式。 regEX.IgnoreCase = True 设置是否区分字母的大小写。 regEX.Global = True 设置全程性质。 Set matches = regEX.Execute(strng) 执行搜索。 For Each match In matches 重复匹配集合 retStr = retStr & Match found at position RetStr=RetStr&Match.FirstIndex&.Match Value is RetStr=RetStr&Match.Value&.&vbCRLF Next RegExpTest = retStr End Function IgnoreCase 属性 设置或返回一个Boolean值，指明模式搜索是否区分大小写。 Object.IgnoreCase = True | False Object 参数总是一个 RegExp 对象。如果搜索是区分大小写的，则 IgnoreCase 属性为 False；否则为 True。缺省值为 False。 说明 下面的代码说明了 IgnoreCase 属性的用法（改变赋予 IgnoreCase 属性的值以观察其效果)：Function RegExpTest2(patrn, strng) Dim regEX, match, matches 建立变量。 Set regEX = New RegExp 建立正则表达式。 regEX.Pattern = patrn 设置模式。 regEX.IgnoreCase = True 设置不区分大小写。 regEX.Global = True 设置全局可用性 Set matches = regEX.Execute(strng) 执行搜索。 For Each match In matches 重复匹配集合 retStr = retStr & Match found at position RetStr=RetStr&Match.FirstIndex&.Match Value is RetStr=RetStr&Match.Value&.&vbCRLF Next RegExpTest = retStrEnd FunctionExecute 方法对指定的字符串执行正则表达式搜索?object.Execute(string)参数Object必选项。总是一个 RegExp 对象的名称。string必选项?要在其上执行正则表达式的文本字符串?说明正则表达式搜索的设计模式是通过 RegExp 对象的 Pattern 来设置的。Execute 方法返回一个 Matches 集合，其中包含了在 string 中找到的每一个匹配的 Match 对象。如果未找到匹配，Execute 将返回空的 Matches 集合。下面的代码说明了 Execute 方法的用法。Function RegExpTest3(patrn, strng) Dim regEX, match, matches Create variable. Set regEX = New RegExp Create a regular expression. regEX.Pattern = patrn Set pattern. regEX.IgnoreCase = True Set case insensitivity. regEX.Global = True Set global applicability. Set matches = regEX.Execute(strng) Execute search. For Each match In matches Iterate Matches collection. retStr = retStr & Match found at position retStr = retStr & match.FirstIndex & . Match Value is retStr = retStr & match.Value & . & vbCrLf Next RegExpTest = retStrEnd FunctionReplace 方法替换在正则表达式查找中找到的文本?object.Replace(string1, string2)参数Object必选项。总是一个 RegExp 对象的名称。string1必选项?string1 是将要进行文本替换的字符串?string2必选项。 string2 是替换文本字符串。说明被替换的文本的实际模式是通过 RegExp 对象的 Pattern 属性设置的。Replace 方法返回 string1 的副本，其中的 RegExp.Pattern 文本已经被替换为 string2。如果没有找到匹配的文本，将返回原来的 string1 的副本。下面的例子说明了 Replace 方法的用法。Function ReplaceTest4(patrn, replStr) Dim regEX, str1 建立变量。 str1 = The quick brown fox jumped over the lazy dog. Set regEX = New RegExp 建立正则表达式。 regEX.Pattern = patrn 设置模式。 regEX.IgnoreCase = True 设置是否区分大小写。 ReplaceTest = regEX.Replace(str1, replStr) 作替换。End FunctionMsgBox(ReplaceTest(fox, cat) 将 fox 替换为 cat。；另外，Replace 方法在模式中替换 subexpressions 。 下面对以前示例中函数的调用，替换了原字符串中的所有字对：MsgBox (ReplaceText(S+)(s+)(S+), $3$2$1) 交换词对.test 方法对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。object.Test(string)参数Object必选项。总是一个 RegExp 对象的名称。string必选项?要执行正则表达式搜索的文本字符串?说明正则表达式搜索的实际模式是通过RegExp对象的Pattern属性来设置的?RegExp.Global属性对Test方法没有影响?如果找到了匹配的模式，Test方法返回True；否则返回False。下面的代码说明了Test 方法的用法?Function RegExpTest5(patrn, strng) Dim regEX, retVal 建立变量。 Set regEX = New RegExp 建立正则表达式。 regEX.Pattern = patrn 设置模式。 regEX.IgnoreCase = False 设置是否区分大小写。 retVal = regEX.Test(strng) 执行搜索测试。 If retVal Then RegExpTest = 找到一个或多个匹配。 Else RegExpTest = 未找到匹配。 End IfEnd FunctionMsgBox (RegExpTest(is., IS1 is2 IS3 is4)Match 对象提供了对正则表达式匹配的只读属性的访问?说明Match 对象只能通过 RegExp 对象的 Execute 方法来创建，该方法实际上返回了 Match 对象的集合。所有的 Match 对象属性都是只读的。在执行正则表达式时，可能产生零个或多个 Match 对象。每个 Match 对象提供了被正则表达式搜索找到的字符串的访问、字符串的长度，以及找到匹配的索引位置等。下面的代码说明了 Match 对象的用法：Function RegExpTest6(patrn, strng) Dim regEX, match, matches 建立变量。 Set regEX = New RegExp 建立正则表达式。 regEX.Pattern = patrn 设置模式。 regEX.IgnoreCase = True 设置是否区分大小写。 regEX.Global = True 设置全局替换。 Set matches = regEX.Execute(strng) 执行搜索。 For Each match In matches 遍历 Matches 集合。 retStr = retStr & Match & i & found at position retStr = retStr & match.FirstIndex & . Match Value is retStr = retStr & match.Value & . & vbCrLf Next RegExpTest = retStrEnd FunctionMsgBox (RegExpTest(is., IS1 is2 IS3 is4)Matches 集合正则表达式 Match 对象的集合。说明Matches 集合中包含若干独立的 Match 对象，只能使用 RegExp 对象的 Execute 方法来创建之。与独立的 Match 对象属性相同，Matches 集合的一个属性是只读的。在执行正则表达式时，可能产生零个或多个 Match 对象。每个 Match 对象都提供了与正则表达式匹配的字符串的访问入口、字符串的长度，以及标识匹配位置的索引。下面的代码将说明如何使用正则表达式查找获得 Matches 集合，以及如何循环遍历集合：Function RegExpTest7(patrn, strng) Dim regEX, match, matches 创建变量。 Set regEX = New RegExp 创建正则表达式。 regEX.Pattern = patrn 设置模式。 regEX.IgnoreCase = True 设置是否区分大小写。 regEX.Global = True 设置全程匹配。 Set matches = regEX.Execute(strng) 执行搜索。 For Each match In matches 循环遍历Matches集合。 retStr = retStr & Match found at position retStr = retStr & match.FirstIndex & . Match Value is retStr = retStr & match.Value & . & vbCrLf Next RegExpTest = retStrEnd FunctionMsgBox (RegExpTest(is., IS1 is2 IS3 is4)SubMatches8 集合正则表达式子匹配字符串的集合?说明SubMatches 集合包含了单个的子匹配字符串，只能用 RegExp 对象的 Execute 方法创建。SubMatches 集合的属性是只读的。运行一个正则表达式时，当圆括号中捕捉到子表达式时可以有零个或多个子匹配。SubMatches 集合中的每一项是由正则表达式找到并捕获的的字符串。下面的代码演示了如何从一个正则表达式获得一个 SubMatches 集合以及如何操作它的专有成员：Sub MatchTest8() inpStr = Dim oRe, oMatch, oMatches Set oRe = New RegExp 查找一个电子邮件地址（不是一个理想的 RegExp） oRe.Pattern = (w+)(w+).(w+) 得到 Matches 集合 Set oMatches = oRe.Execute(inpStr) 因为只有一个地址，所以，集合只有一项，就是索引=0的那个 得到 Matches 集合中的第一项 Set oMatch = oMatches(0) 创建结果字符串。 Match 对象是完整匹配 retStr = 电子邮件地址是： & oMatch & vbNewLine 得到地址的子匹配部分。 retStr = retStr & 电子邮件别名是： & oMatch.SubMatches(0) dragon retStr = retStr & vbNewLine retStr = retStr & 组织是： & oMatch.SubMatches(1) xyzzy SubMatchTest = retStr MsgBox retStrEnd SubMsgBox(SubMatchTest(请写信到 。 谢谢！)Sub jj1()把数字筛选出来 不包含括号的数字 Dim kk() Set x = CreateObject(vbscript.regexp) x.Global = 1 x.Pattern = (.*?)(.*? j = 1 For Each y In a2:h2 ReDim Preserve kk(1 To j) If x.Test(y) Then Set mchs = x.Execute(y) Set mc = mchs(0) retStr = mc.SubMatches(0) kk(j) = Val(retStr) j = j + 1 End If Next MsgBox Application.Min(kk)End SubSub jj()把数字筛选出来 不包含括号的数字 Dim kk() Set x = CreateObject(vbscript.regexp) x.Global = 1 x.Pattern = (.*?)(.*? j = 1 For Each y In a2:h2 ReDim Preserve kk(1 To j) If x.Test(