正则表达式 - 如何测试 2 个 str 模式并根据哪个 str 模式匹配进行替换

Question

我目前在 Excel VBA 中有两个功能独立的子程序。每个子搜索不同的字符串模式，然后进行替换。

Sub 1 搜索目标字符串中的前导 0，将其删除，并将内容放入单独的单元格中。

Sub 2 在目标字符串中搜索终结符“99”，用 X 替换“99”，并将内容放入单独的单元格中。

我执行此特定操作的方法是首先运行 Sub1。结果放置在 AO 列中。然后，我根据从 Sub1 获得的结果运行 Sub2，并将这些结果放在下一个相邻列中。

我想将两个子程序结合起来，只运行一次即可获得所需的结果。

以下是 W 列中我正在应用正则表达式的目标字符串的示例:

098765-9876-77
333222-7777-G5
9876-078-99
9867x77A

子1

Sub tom_briggs_test_leading_zero()
'This sub searches for a leading zero in the target string and removes it.

Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("w2:w73352")

For Each cell In Myrange
    strPattern = "^0(.*)"
    If strPattern <> "" Then
    strInput = cell.Value
    strReplace = "$1"

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.Test(strInput) Then
        cell.Offset(0, 18) = regEx.Replace(strInput, strReplace)

    Else
        cell.Offset(0, 18) = strInput
    End If
End If
Next
End Sub

子2

Sub tom_briggs_test_trailing_99()
'This sub searchs for teriminal 99s in the target string and replaces them 
'with -XX.

Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("AO2:AO73352")
'AO is the column where results from Sub1 have been placed

For Each cell In Myrange
    strPattern = "(.*)-99$"
    If strPattern <> "" Then
    strInput = cell.Value
    strReplace = "$1-XX"

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.Test(strInput) Then
        cell.Offset(0, 1) = regEx.Replace(strInput, strReplace)

    Else
        cell.Offset(0, 1) = strInput
    End If
End If
Next
End Sub

感谢您的考虑。

Answer 1

这个怎么样:

Sub tom_briggs_fix_head_and_tail()
    'This sub removes a leading zero in the target string and
    'replaces trailing 99s in the target string with -XX.

    Dim regExHead As New RegExp
    Dim strHeadPattern As String
    Dim strHeadReplace As String

    Dim regExTail As New RegExp
    Dim strTailPattern As String
    Dim strTailReplace As String

    Dim strInput As String
    Dim Myrange As Range
    Dim c As Range

    Set Myrange = ActiveSheet.Range("w2:w73352")
    strHeadPattern = "^0(.*)"
    strHeadReplace = "$1"
    strTailPattern = "(.*)-99$"
    strTailReplace = "$1-XX"

    With regExHead
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strHeadPattern
    End With

    With regExTail
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strTailPattern
    End With

    For Each c In Myrange
        strInput = c.Value
        strInput = IIf(regExHead.Test(strInput), _
                regExHead.Replace(strInput, strHeadReplace), strInput)
        strInput = IIf(regExTail.Test(strInput), _
                regExTail.Replace(strInput, strTailReplace), strInput)
        c.Offset(0, 19) = strInput
    Next
End Sub

希望有帮助