excel - 在 Excel 中为字符串生成正则表达式

Question

我有一个巨大的字符串列表，我试图以自动方式生成正则表达式。字符串非常简单，我想使用公式或 vba 代码生成正则表达式。从字符串列表中，有以下图例:

& - Any UPPERCASE character (A-Z)
# - Any digits (0-9)
_ - Space (/s)
- - Dash

例如，为以下字符串生成的正则表达式:

Policy Number                               Policy Digits   Regular Expression
####&&######                                12              ^\d{4}[A-Z]{2}\d{6}$
####&_######                                11              ^\d{4}[A-Z]{1}\s\d{6}$
ACPBP&&##########                           17              ^[ACPBP]{5}[A-Z]{2}\d{10}$
ACPBA&########## or ACPBA&&##########       16 or 17        ^[ACPBA]{5}[A-Z]{1,2}\d{10}$
##########                                  10              ^\d{10}$
09############                              14              ^[09]{2}\d{12}$
A&&######, A&&#######, or A&&########       9, 10 or 11     ^[A]{1}[A-Z]{2}\d{6,8}$
&&&####, &&&#####, or &&&######             7, 8, or 9      ^[A-Z]{3}\d{4,6}$
09-##########-##                            14              ^[09]{2}-\d{10}-\d{2}$

是否有一些现有代码可用于为大量字符串生成正则表达式？我可以使用哪些提示或技巧来构建正则表达式字符串？提前致谢。

Answer 1

没有现有代码，但试试这个:

Option Explicit
Option Compare Text 'to handle upper and lower case "or"
'Set reference to Microsoft Scripting Runtime
'  or use Late Binding if distributing this
Function createRePattern(sPolicyNum As String) As String
    Dim dCode As Dictionary, dReg As Dictionary
    Dim I As Long, sReg As String, s As String
    Dim v, sPN
    
v = Replace(sPolicyNum, "or", ",")
v = Split(v, ",")

Set dCode = New Dictionary
    dCode.Add Key:="&", Item:="[A-Z]"
    dCode.Add Key:="#", Item:="\d"
    dCode.Add Key:="_", Item:="\s"
    
For Each sPN In v
sPN = Trim(sPN)
If Not sPN = "" Then

Set dReg = New Dictionary
For I = 1 To Len(sPN)
    s = Mid(sPN, I, 1)
    If Not dCode.Exists(s) Then dCode.Add s, s
    If dReg.Exists(s) Then
        dReg(s) = dReg(s) + 1
    Else
        If dReg.Count = 1 Then
            dReg.Add s, 1
            s = Mid(sPN, I - 1, 1)
            sReg = sReg & dCode(s) & IIf(dReg(s) > 1, "{" & dReg(s) & "}", "")
            dReg.Remove s
        Else
            dReg.Add s, 1
        End If
    End If
Next I

'Last Entry in Regex
s = Right(sPN, 1)
sReg = sReg & dCode(s) & IIf(dReg(s) > 1, "{" & dReg(s) & "}", "") & "|"

End If
Next sPN

s = Left(sReg, Len(sReg) - 1)

'Non-capturing group added if alternation present
If InStr(s, "|") = 0 Then
    sReg = "^" & s & "$"
Else
    sReg = "^(?:" & Left(sReg, Len(sReg) - 1) & ")$"
End If

createRePattern = sReg

End Function

笔记
如所写，存在不能引用文字字符串的限制: # , & , _ , , or