芒果

[转帖] 超级好用的一个正则表达式自定义函数
原帖 转译自:https://stackoverflow.com/questions/22542834/how-...
扫描右侧二维码阅读全文
20
2020/04

[转帖] 超级好用的一个正则表达式自定义函数

原帖

如何使用自定义函数请自行搜索...

操作符表:

Order Name Representation --- --- --- 1 成组 ( )

2 成群 ? + * {m,n} {m, n}?

3 锚点 abc ^ $

4 或

5 取组 $0-$9

字符表:

abr same as meaning


\d [0-9] 数字

\D [^0-9] 非数字

\w [a-zA-Z0-9_] 数字和字母

\W [^a-zA-Z0-9_] 非数字字母

\s [ \r\t
\f] 空白

\S [^ \r\t
\f] 非空白

[
] 新行

  • 几个例子:

=regex("Peter Gordon: some@email.com, 47", "\w+@\w+.\w+")

=regex("Peter Gordon: some@email.com, 47", "\w+@\w+.\w+", "$0")

Results in: some@email.com

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Results in: E-Mail: some@email.com, Name: Peter Gordon

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)

Results in: Peter Gordon some@email.com .
Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant

    Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
    Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
    Dim replaceNumber As Integer

    With inputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = matchPattern
    End With
    With outputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "\$(\d+)"
    End With
    With outReplaceRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With

    Set inputMatches = inputRegexObj.Execute(strInput)
    If inputMatches.count = 0 Then
        regex = False
    Else
        Set replaceMatches = outputRegexObj.Execute(outputPattern)
        For Each replaceMatch In replaceMatches
            replaceNumber = replaceMatch.SubMatches(0)
            outReplaceRegexObj.Pattern = "\$" & replaceNumber

            If replaceNumber = 0 Then
                outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
            Else
                If replaceNumber > inputMatches(0).SubMatches.count Then
                    'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                    regex = CVErr(xlErrValue)
                    Exit Function
                Else
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                End If
            End If
        Next
        regex = outputPattern
    End If
End Function
Last modification:April 20th, 2020 at 03:20 pm
If you think my article is useful to you, please feel free to appreciate

3 comments

  1. cialis generic

    cialis dosage when to take top referrers - cialis economico - cialis information last post

  2. tadalafil side effects

    cialis 5mg generic total posts - buy cialis british dragon - lily icos cialis

  3. cialis otc

    meth cialis and poppers - why professional cialis - cialis 5mg in total there are

Leave a Comment