他山之石——在VBA中使用正则表达式-Part3（Regular Expression）

成为Excel精英真不容易啊，正则表达式必须拿下！

'()
  '可以让括号内作为一个整体产生重复
   
        Sub t29()
           Dim regx As New RegExp
           Dim sr
           sr = "A3A3QA3A37BDFE87A8"
           With regx
            .Global = True
            .Pattern = "((A3){2})" '相当于A3A3
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub
  '取匹配结果的时候，括号中的表达式可以用 \数字引用

        Sub t30()
           Dim regx As New RegExp
           Dim sr
           sr = "A3A3QA3A37BDFE87A8"
           With regx
            .Global = True
            .Pattern = "((A3){2})Q\1"
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub
          Sub t31()
           Dim regx As New RegExp
           Dim sr
           sr = "A3A3B4B4QB4B47BDFE87A8"
           With regx
            .Global = True
            .Pattern = "((A3){2})((B4){2})Q\4"
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub
        
'用(?=字符)可以先进行预测查找，到一个匹配项后，将在匹配文本之前开始搜索下一个匹配项。 不会保存匹配项以备将来之用。
  
  '例：截取某个字符之前的数据
      Sub t343()
        Dim regex As New RegExp
        Dim sr, mat, m
        sr = "100元8000元57元"
        With regex
          .Global = True
           .Pattern = "\d+(?=元)" '查找任意多数字后的元，查找到后从元以前开始查找（因为元前的数字已被使用，
                                  '所以只能从元开始查找）匹配 ()后面的，因为后面没有设置，所以只显示前面的数字，元不再显示
            Set mat = .Execute(sr)
            For Each m In mat
              Debug.Print m
            Next m
        End With
      End Sub
   '例：验证密码，条件是4-8位，必须包含一个数字
      Sub t355()
        Dim regex As New RegExp
        Dim sr, mat, m
        sr = "A8ayaa"
        With regex
          .Global = True
           .Pattern = "^(?=.*\d).{4,8}$"
            Set mat = .Execute(sr)
            For Each m In mat
              Debug.Print m
            Next m
        End With
      End Sub
      
'用(?!字符)可以先进行负预测查找，到一个匹配项后，将在匹配文本之前开始搜索下一个匹配项。 不会保存匹配项以备将来之用。
     Sub t356()
        Dim regex As New RegExp
        Dim sr, mat, m
        sr = "中国建筑集团公司"
        With regex
          .Global = True
           .Pattern = "^(?!中国).*"
            Set mat = .Execute(sr)
            For Each m In mat
              Debug.Print m
            Next m
        End With
      End Sub
 
'()与|一起使用可以表示or

      Sub t344()
        Dim regex As New RegExp
        Dim sr, mat, m
        sr = "100元800块7元"
        With regex
          .Global = True
           .Pattern = "\d+(元|块)"
           '.Pattern = "\d+(?=元|块)"
            Set mat = .Execute(sr)
            For Each m In mat
              Debug.Print m
            Next m
        End With
      End Sub

'[]
 '使用方括号 [ ] 包含一系列字符，能够匹配其中任意一个字符。用 [^ ] 不包含一系列字符，
 '则能够匹配其中字符之外的任意一个字符。同样的道理，虽然可以匹配其中任意一个，但是只能是一个，不是多个
 
  '1 和括号内的其中一个匹配
        Sub t29()
           Dim regx As New RegExp
           Dim sr
           sr = "ABDC"
           With regx
            .Global = True
            .Pattern = "[BC]"
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub
        
   '2 非括号内的字符
        
        Sub T35()
           Dim regx As New RegExp
           Dim sr
           sr = "ABCDBDC"
           With regx
            .Global = True
            .Pattern = "[^BC]"
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub
   '3 在一个区间
        Sub t38()
           Dim regx As New RegExp
           Dim sr
           sr = "ABCDGWDFUFE"
           With regx
            .Global = True
            .Pattern = "[a-h]"
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub
        Sub t40()
           Dim regx As New RegExp
           Dim sr
           sr = "124325436789"
           With regx
            .Global = True
            .Pattern = "[1-47-9]"
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub
'^符号：限制的字符在最前面,如^\d表示以数字开头
 
    Sub T34()
        Dim regex As New RegExp
        Dim sr, mat, m
        sr = "d234我345d43"
        With regex
          .Global = True
          .Pattern = "^\d*"
            Set mat = .Execute(sr)
            For Each m In mat
              Debug.Print m
            Next m
        End With
      End Sub

'$符号：限制的字符在最后面，如 A$表示最后一个字符是A

   
    Sub T3433()
        Dim regex As New RegExp
        Dim sr, mat, m
        sr = "R243r"
        With regex
          .Global = True
           .Pattern = "^\D.*\D$"
            Set mat = .Execute(sr)
            For Each m In mat
              Debug.Print m
            Next m
        End With
      End Sub

'\b
  '空格(包含开头和结尾)
  
        Sub t26()
           Dim regx As New RegExp
           Dim sr
           sr = "A12dA56 A4"
           With regx
            .Global = True
            .Pattern = "\bA\d+"
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub
    
    Sub T272()
        Dim regex As New RegExp
        Dim sr, mat, m
        sr = "ad bf cr de ee"
        With regex
          .Global = True
           .Pattern = ".+?\b"
            Set mat = .Execute(sr)
            For Each m In mat
              If m <> " " Then Debug.Print m
            Next m
        End With
      End Sub
'|
 '可以设置两个条件,匹配左边或右边的
        Sub t27()
           Dim regx As New RegExp
           Dim sr
           sr = "A12DA56 A4B34D"
           With regx
            .Global = True
            .Pattern = "A\d+|B\d+"
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub
'\un 匹配 n，其中 n 是以四位十六进制数表示的 Unicode 字符。
'汉字一的编码是4e00,最后一个代码是9fa5
       Sub t2722()
           Dim regx As New RegExp
           Dim sr
           sr = "A12d我A爱56你 A4"
           With regx
            .Global = True
            .Pattern = "[\u4e00-\u9fa5]"
            Debug.Print .Replace(sr, "")
           End With
           
        End Sub