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

1、EXCEL VBA 正则表达式 从实例开始所有代码都测试过2011-09-09：/dxy1982/archive/2011/09/09/2159246.html2010-08-08：/thread-137472-1-1.html数据验证经常出现的情况是，需要验证和处理大量的文本内容，有时候需要查找字符串，有时候要按条件替换，并且这些待处理的问题还是有一定的规律可循的，但是无法通过枚举的方式挨个验证，这个时候就需要正则表达式来帮忙。1.正则表达式(Regular Expression)基础正则表达式是通用的文本搜

2、索和处理方案，它的知识不是VBA独有的，基本上每种语言都内置了正则表达式的功能。正则表达式的基础知识不是这里的重点，需要的朋友可以Google一下，或者参看下面的一些入门教程：/tutorials/regex/regex.htm/zh/regref.htm/archives/433.html2.VBA中的正则表达式应用在VBA中使用正则表达式，可以通过下列途径实现：创建正则表达式对象:前期绑定：在VBA代码编辑器中的Tools菜单中，选中References.，然

3、后引用Microsoft VBScript Regular Expressions 5.5类库，然后直接定义对象：Dim reg As New RegExp。后期绑定：使用CreateObject方法定义对象：CreateObject(VBSCRIPT.REGEXP)。前一种方式的优点是可以有编辑器的Intellisense支持?RegExp对象的属性:Global - 设置或返回一个 Boolean 值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。如果搜索应用于整个字符串，Global 属性的值应该为 True，否则其值为 False。默认的设置为True。Multiline

4、- 返回正则表达式是否具有标志m, 缺省值为False。如果指定的搜索字符串分布在多行，这个属性是要设置为True的。IgnoreCase - 设置或返回一个Boolean值，指明模式搜索是否区分大小写。如果搜索是区分大小写的，则 IgnoreCase 属性应该为False；否则应该设为True。缺省值为True。Pattern - 设置或返回被搜索的正则表达式模式。 被搜索的正则字符串表达式。它包含各种正则表达式字符。RegExp对象的方法:Execute - 对指定的字符串执行正则表达式搜索。需要传入要在其上执行正则表达式的文本字符串。正则表达式搜索的设计模式是通过 RegExp对象的Pa

5、ttern来设置的。Execute方法返回一个Matches集合，其中包含了在string中找到的每一个匹配的Match对 象。如果未找到匹配，Execute将返回空的Matches集合。Replace -替换在正则表达式查找中找到的文本?Test - 对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。RegExp.Global属性对Test方法没有影响。如果找到了匹配的模式，Test方法返回True；否则返回False。MatchCollection对象与Match对象匹配到的所有对象放在MatchCollection集合中，这个集合对象只有两个只读

6、属性：Count: 匹配到的对象的数目Item：集合的又一通用方法，需要传入Index值获取指定的元素。一般，可以使用For Each语句枚举集合中的对象。集合中对象的类型是Match。Match对象有以下几个只读的属性:FirstIndex - 匹配字符串在整个字符串中的位置，值从0开始。Length -匹配字符串的长度?Value -匹配的字符串?SubMatches - 集合，匹配字符串中每个分组的值。作为集合类型，有Count和Item两个属性。常用的正则表达式主要有以下几种:匹配中文字符的正则表达式: u4e00-u9fa5评注：匹配中文还真是个头疼的事，有了这个表达式就好办了匹配双

7、字节字符 (包括汉字在内): x00-xff评注：可以用来计算字符串的长度（一个双字节字符长度计2，ASCII字符计1）匹配空白行的正则表达式：ns*r评注: 可以用来删除空白行匹配HTML标记的正则表达式：*.*?|评注：网上流传的版本太糟糕，上面这个也仅仅能匹配部分，对于复杂的嵌套标记依旧无能为力匹配首尾空白字符的正则表达式：s*|s*$评注：可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式匹配Email地址的正则表达式：w+(-+.w+)*w+(-.w+)*.w+(-.w+)*评注: 表单验证时很实用匹配网址URL的正则表达式：a-zA-z+:/s*评注：

8、网上流传的版本功能很有限，上面这个基本可以满足需求匹配帐号是否合法(字母开头，允许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*$ /匹配负整

9、数-?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个英文字母组成

10、的字符串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.常

11、用的反义代码代码/语法 说明W 匹配任意不是字母，数字，下划线，汉字的字符S 匹配任意不是空白符的字符D 匹配任意非数字的字符B 匹配不是单词开头或结束的位置x 匹配除了x以外的任意字符aeiou 匹配除了aeiou这几个字母以外的任意字符表4.常用分组语法代码/语法 说明 分类(exp) 匹配exp,并捕获文本到自动命名的组里 捕获(?exp) 匹配exp,并捕获文本到名称为name的组里，也可以写成(?nameexp)(?:exp) 匹配exp,不捕获匹配的文本，也不给此分组分配组号(?=exp) 匹配exp前面的位置 零宽断言(?=exp) 匹配exp后面的位置(?!exp) 匹配后面跟

12、的不是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代码编

13、辑器中的Tools菜单中，选中References.，然后引用Microsoft VBScript Regular Expressions 5.5类库，然后直接定义对象：Dim reg As New RegExp。后期绑定：使用CreateObject方法定义对象：CreateObject(VBSCRIPT.REGEXP)。前一种方式的优点是可以有编辑器的Intellisense支持?RegExp对象的属性:Global - 设置或返回一个 Boolean 值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。如果搜索应用于整个字符串，Global 属性的值应该为 True，否则其值为

14、 False。默认的设置为True。Multiline - 返回正则表达式是否具有标志m, 缺省值为False。如果指定的搜索字符串分布在多行，这个属性是要设置为True的。IgnoreCase - 设置或返回一个Boolean值，指明模式搜索是否区分大小写。如果搜索是区分大小写的，则 IgnoreCase 属性应该为False；否则应该设为True。缺省值为True。Pattern - 设置或返回被搜索的正则表达式模式。 被搜索的正则字符串表达式。它包含各种正则表达式字符。RegExp对象的方法:Execute - 对指定的字符串执行正则表达式搜索。需要传入要在其上执行正则表达式的文本字符串

15、。正则表达式搜索的设计模式是通过 RegExp对象的Pattern来设置的。Execute方法返回一个Matches集合，其中包含了在string中找到的每一个匹配的Match对象。如果未找到匹配，Execute将返回空的Matches集合。Replace -替换在正则表达式查找中找到的文本?Test - 对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。RegExp.Global属性对Test方法没有影响。如果找到了匹配的模式，Test方法返回True；否则返回False。MatchCollection对象与Match对象匹配到的所有对象放在Matc

16、hCollection集合中，这个集合对象只有两个只读属性：Count: 匹配到的对象的数目Item：集合的又一通用方法，需要传入Index值获取指定的元素。一般，可以使用For Each语句枚举集合中的对象。集合中对象的类型是Match。Match对象有以下几个只读的属性:FirstIndex - 匹配字符串在整个字符串中的位置，值从0开始。Length -匹配字符串的长度?Value -匹配的字符串?SubMatches - 集合，匹配字符串中每个分组的值。作为集合类型，有Count和Item两个属性。正则表达式很强大，是处理文本的首选，不管你信不信，反正我是信了。说了很多，最后以一个小例

17、子结尾吧: 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 Stri

18、ng) 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

19、 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.REG

20、EXP) 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是否区别大小写返回参数，返

21、回一个逻辑值，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

22、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 建立

23、变量。 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

24、 retStr = retStr & match.Value & . & vbCrLf Next RegExpTest = retStrEnd FunctionGlobal 属性设置或返回一个 Boolean 值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。Object.Global = True | False 对象 参数总是 RegExp 对象。如果搜索应用于整个字符串，Global 属性的值为 True，否则其值为 False。默认的设置为 False。下面的代码说明了 Global 属性的用法（改变赋予 Global 属性的值并观察其效果）：Function RegExp

25、Test1(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

26、=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 属性的用法

27、（改变赋予 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 =

28、 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 来设置的

29、。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 =

30、 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

31、.Value & . & vbCrLf Next RegExpTest = retStrEnd FunctionReplace 方法替换在正则表达式查找中找到的文本?object.Replace(string1, string2)参数Object必选项。总是一个 RegExp 对象的名称。string1必选项?string1 是将要进行文本替换的字符串?string2必选项。 string2 是替换文本字符串。说明被替换的文本的实际模式是通过 RegExp 对象的 Pattern 属性设置的。Replace 方法返回 string1 的副本，其中的 RegExp.Pattern 文本已经被替换

32、为 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(

33、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必选项?要执

34、行正则表达式搜索的文本字符串?说明正则表达式搜索的实际模式是通过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 = r

35、egEX.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 对象提供了

36、被正则表达式搜索找到的字符串的访问、字符串的长度，以及找到匹配的索引位置等。下面的代码说明了 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 m

37、atch 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 集合中包含若干独立的

38、 Match 对象，只能使用 RegExp 对象的 Execute 方法来创建之。与独立的 Match 对象属性相同，Matches 集合的一个属性是只读的。在执行正则表达式时，可能产生零个或多个 Match 对象。每个 Match 对象都提供了与正则表达式匹配的字符串的访问入口、字符串的长度，以及标识匹配位置的索引。下面的代码将说明如何使用正则表达式查找获得 Matches 集合，以及如何循环遍历集合：Function RegExpTest7(patrn, strng) Dim regEX, match, matches 创建变量。 Set regEX = New RegExp 创建正则表达

39、式。 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.Valu

40、e & . & vbCrLf Next RegExpTest = retStrEnd FunctionMsgBox (RegExpTest(is., IS1 is2 IS3 is4)SubMatches8 集合正则表达式子匹配字符串的集合?说明SubMatches 集合包含了单个的子匹配字符串，只能用 RegExp 对象的 Execute 方法创建。SubMatches 集合的属性是只读的。运行一个正则表达式时，当圆括号中捕捉到子表达式时可以有零个或多个子匹配。SubMatches 集合中的每一项是由正则表达式找到并捕获的的字符串。下面的代码演示了如何从一个正则表达式获得一个 SubMatch

41、es 集合以及如何操作它的专有成员：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 对

42、象是完整匹配 retStr = 电子邮件地址是： & oMatch & vbNewLine 得到地址的子匹配部分。 retStr = retStr & 电子邮件别名是： & oMatch.SubMatches(0) dragon retStr = retStr & vbNewLine retStr = retStr & 组织是： & oMatch.SubMatches(1) xyzzy SubMatchTest = retStr MsgBox retStrEnd SubMsgBox(SubMatchTest(请写信到 。 谢谢！)

43、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(