1、EXCEL VBA 正則表達式 從實例開始所有代碼都測試過2011-09-09：2010-08-08：數據驗證經常出現的情況是，需要驗證和處理大量的文本內容，有時候需要查找字符串，有時候要按條件替換，并且這些待處理的問題還是有一定的規律可循的，但是無法通過枚舉的方式挨個驗證，這個時候就需要正則表達式來幫忙。1.正則表達式(Regular Expression)基礎正則表達式是通用的文本搜索和處理方案，它的知識不是VBA獨有的，基本上每種語言都內置了正則表達式的功能。正則表達式的基礎知識不是這里的重點，需要的朋友可以Google一下，或者參看下面的一些入門教程：http:/www.william

2、/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

3、- 設置或返回一個 Boolean 值，該值指明在整個搜索字符串時模式是全部匹配還是只匹配第一個。如果搜索應用于整個字符串，Global 屬性的值應該為 True，否則其值為 False。默認的設置為True。Multiline - 返回正則表達式是否具有標志m, 缺省值為False。如果指定的搜索字符串分布在多行，這個屬性是要設置為True的。IgnoreCase - 設置或返回一個Boolean值，指明模式搜索是否區分大小寫。如果搜索是區分大小寫的，則 IgnoreCase 屬性應該為False；否則應該設為True。缺省值為True。Pattern - 設置或返回被搜索的正則表達式模式。

4、 被搜索的正則字符串表達式。它包含各種正則表達式字符。RegExp對象的方法:Execute - 對指定的字符串執行正則表達式搜索。需要傳入要在其上執行正則表達式的文本字符串。正則表達式搜索的設計模式是通過 RegExp對象的Pattern來設置的。Execute方法返回一個Matches集合，其中包含了在string中找到的每一個匹配的Match對 象。如果未找到匹配，Execute將返回空的Matches集合。Replace -替換在正則表達式查找中找到的文本?Test - 對指定的字符串執行一個正則表達式搜索，并返回一個 Boolean 值指示是否找到匹配的模式。RegExp.Globa

5、l屬性對Test方法沒有影響。如果找到了匹配的模式，Test方法返回True；否則返回False。1 / 24MatchCollection對象與Match對象匹配到的所有對象放在MatchCollection集合中，這個集合對象只有兩個只讀屬性：Count: 匹配到的對象的數目Item：集合的又一通用方法，需要傳入Index值獲取指定的元素。一般，可以使用For Each語句枚舉集合中的對象。集合中對象的類型是Match。Match對象有以下幾個只讀的屬性:FirstIndex - 匹配字符串在整個字符串中的位置，值從0開始。Length -匹配字符串的長度?Value -匹配的字符串?Su

6、bMatches - 集合，匹配字符串中每個分組的值。作為集合類型，有Count和Item兩個屬性。常用的正則表達式主要有以下幾種:匹配中文字符的正則表達式: u4e00-u9fa5評注：匹配中文還真是個頭疼的事，有了這個表達式就好辦了匹配雙字節字符 (包括漢字在內): x00-xff評注：可以用來計算字符串的長度（一個雙字節字符長度計2，ASCII字符計1）匹配空白行的正則表達式：ns*r評注: 可以用來刪除空白行匹配HTML標記的正則表達式：*.*?|評注：網上流傳的版本太糟糕，上面這個也僅僅能匹配部分，對于復雜的嵌套標記依舊無能為力匹配首尾空白字符的正則表達式：s*|s*$評注：可以用來

7、刪除行首行尾的空白字符(包括空格、制表符、換頁符等等)，非常有用的表達式匹配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開始匹配

8、中國郵政編碼：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+

9、|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 匹配任意的空

10、白符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) 匹配ex

11、p,并捕獲文本到名稱為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(?(n

12、ame)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)。前一種方式的優點是可以有編輯器的

13、Intellisense支持?RegExp對象的屬性:Global - 設置或返回一個 Boolean 值，該值指明在整個搜索字符串時模式是全部匹配還是只匹配第一個。如果搜索應用于整個字符串，Global 屬性的值應該為 True，否則其值為 False。默認的設置為True。Multiline - 返回正則表達式是否具有標志m, 缺省值為False。如果指定的搜索字符串分布在多行，這個屬性是要設置為True的。IgnoreCase - 設置或返回一個Boolean值，指明模式搜索是否區分大小寫。如果搜索是區分大小寫的，則 IgnoreCase 屬性應該為False；否則應該設為True。缺省

14、值為True。Pattern - 設置或返回被搜索的正則表達式模式。 被搜索的正則字符串表達式。它包含各種正則表達式字符。RegExp對象的方法:Execute - 對指定的字符串執行正則表達式搜索。需要傳入要在其上執行正則表達式的文本字符串。正則表達式搜索的設計模式是通過 RegExp對象的Pattern來設置的。Execute方法返回一個Matches集合，其中包含了在string中找到的每一個匹配的Match對象。如果未找到匹配，Execute將返回空的Matches集合。Replace -替換在正則表達式查找中找到的文本?Test - 對指定的字符串執行一個正則表達式搜索，并返回一個

15、Boolean 值指示是否找到匹配的模式。RegExp.Global屬性對Test方法沒有影響。如果找到了匹配的模式，Test方法返回True；否則返回False。MatchCollection對象與Match對象匹配到的所有對象放在MatchCollection集合中，這個集合對象只有兩個只讀屬性：Count: 匹配到的對象的數目Item：集合的又一通用方法，需要傳入Index值獲取指定的元素。一般，可以使用For Each語句枚舉集合中的對象。集合中對象的類型是Match。Match對象有以下幾個只讀的屬性:FirstIndex - 匹配字符串在整個字符串中的位置，值從0開始。Length

16、 -匹配字符串的長度?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(123aaaaa987u

17、iiui999) 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

18、.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是搜索的數量

19、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 FunctionFun

20、ction 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 設

21、置是否區分大小寫。 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

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

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

24、 對象。如果搜索應用于整個字符串，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 設置全程性質。 S

25、et 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 =

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

27、 設置不區分大小寫。 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 方法對指定的字符串執行正則表達式搜索?

28、object.Execute(string)參數Object必選項?？偸且粋€ RegExp 對象的名稱。string必選項?要在其上執行正則表達式的文本字符串?說明正則表達式搜索的設計模式是通過 RegExp 對象的 Pattern 來設置的。Execute 方法返回一個 Matches 集合，其中包含了在 string 中找到的每一個匹配的 Match 對象。如果未找到匹配，Execute 將返回空的 Matches 集合。下面的代碼說明了 Execute 方法的用法。Function RegExpTest3(patrn, strng) Dim regEX, match, matches C

29、reate 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

30、. 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 是將要進行文本替換的字符串?s

31、tring2必選項。 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. Se

32、t 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

33、) 交換詞對.test 方法對指定的字符串執行一個正則表達式搜索，并返回一個 Boolean 值指示是否找到匹配的模式。object.Test(string)參數Object必選項?？偸且粋€ RegExp 對象的名稱。string必選項?要執行正則表達式搜索的文本字符串?說明正則表達式搜索的實際模式是通過RegExp對象的Pattern屬性來設置的?RegExp.Global屬性對Test方法沒有影響?如果找到了匹配的模式，Test方法返回True；否則返回False。下面的代碼說明了Test 方法的用法?Function RegExpTest5(patrn, strng) Dim regEX

34、, 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 對象提供了對正則表達式匹配的只讀屬性的訪問?說明Ma

35、tch 對象只能通過 RegExp 對象的 Execute 方法來創建，該方法實際上返回了 Match 對象的集合。所有的 Match 對象屬性都是只讀的。在執行正則表達式時，可能產生零個或多個 Match 對象。每個 Match 對象提供了被正則表達式搜索找到的字符串的訪問、字符串的長度，以及找到匹配的索引位置等。下面的代碼說明了 Match 對象的用法：Function RegExpTest6(patrn, strng) Dim regEX, match, matches 建立變量。 Set regEX = New RegExp 建立正則表達式。 regEX.Pattern = patrn

36、 設置模式。 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 Nex

37、t RegExpTest = retStrEnd FunctionMsgBox (RegExpTest(is., IS1 is2 IS3 is4)Matches 集合正則表達式 Match 對象的集合。說明Matches 集合中包含若干獨立的 Match 對象，只能使用 RegExp 對象的 Execute 方法來創建之。與獨立的 Match 對象屬性相同，Matches 集合的一個屬性是只讀的。在執行正則表達式時，可能產生零個或多個 Match 對象。每個 Match 對象都提供了與正則表達式匹配的字符串的訪問入口、字符串的長度，以及標識匹配位置的索引。下面的代碼將說明如何使用正則表達式查找

38、獲得 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集合。 ret

39、Str = 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 方法創建。Sub

40、Matches 集合的屬性是只讀的。運行一個正則表達式時，當圓括號中捕捉到子表達式時可以有零個或多個子匹配。SubMatches 集合中的每一項是由正則表達式找到并捕獲的的字符串。下面的代碼演示了如何從一個正則表達式獲得一個 SubMatches 集合以及如何操作它的專有成員：Sub MatchTest8() inpStr = dragon Dim oRe, oMatch, oMatches Set oRe = New RegExp 查找一個電子郵件地址（不是一個理想的 RegExp） oRe.Pattern = (w+)(w+).(w+) 得到 Matches 集合 Set oMatches

41、 = oRe.Execute(inpStr) 因為只有一個地址，所以，集合只有一項，就是索引=0的那個 得到 Matches 集合中的第一項 Set oMatch = oMatches(0) 創建結果字符串。 Match 對象是完整匹配 dragon retStr = 電子郵件地址是： & oMatch & vbNewLine 得到地址的子匹配部分。 retStr = retStr & 電子郵件別名是： & oMatch.SubMatches(0) dragon retStr = retStr & vbNewLine retStr = retStr & 組織是： & oMatch.SubMat

42、ches(1) xyzzy SubMatchTest = retStr MsgBox retStrEnd SubMsgBox(SubMatchTest(請寫信到 dragon 。 謝謝！)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 Pre