Vfrdtyky

In Excel VBA I need to perform multiple regular expression matches which then deletes the match from the string while preserving the remainder of the string. I have it working by daisy-chaining two variables, and by not testing the pattern match first since the second match is the remainder of the first.

Consider the follow data:

(2.5.3) A. 100% of product will be delivered in 3 days

(2.5.3) B. Capability to deliver product by air.

(2.5.3) C. Support for xyz feature

(2.5.3) D. Vendor is to provide an overview of the network as proposed.

(2.5.3) E. The network should allow CustomerABC to discover their devices.

(2.5.3) F. The use of CustomerABC existing infrastructure should be optimized. CustomerABC's capability will vary.

(2.5.3) G. Describe the number of network devices requiring to run CustomerABC's center.

With this data, I am deleting the outline numbers in the beginning of the string, as well as any references to CustomerABC and any hyphenation that could possibly appear multiple times in the string at any location, with potentially upper and lower case. I have the regex's working. Here is the code I'm trying:

Function test(Txt As String) As String

Dim regEx As Object

Dim v1 As String

Dim v2 As String

Dim n As String

n = "CustomerABC"



If regEx Is Nothing Then

    Set regEx = CreateObject("VBScript.RegExp")

    regEx.Global = True

    regEx.IgnoreCase = True

End If

If Len(Txt) > 0 Then

    With regEx

    ' The 1st pattern

    .Pattern = "^(?[0-9.]+)?"

    'If Not .Test(Txt) Then Exit Function

    v1 = .Replace(Txt, "")

    ' The 2nd pattern

    .Pattern = n + "(S*)?(s+)?"

    'If Not .Test(Txt) Then Exit Function

    v2 = .Replace(v1, "")

 ' The result

   test = Application.Trim(v2)

   End With

  End If

End Function

Is there a way to make this better, speed things up, and have a variable number of match/deletions?

Thanks in advance.

Like this:

Function test(Txt As String) As String

    Static regEx As Object '<< need Static here

    Dim rv As String, p, n



    n = "CustomerABC"



    If regEx Is Nothing Then

        Set regEx = CreateObject("VBScript.RegExp")

        regEx.Global = True

        regEx.IgnoreCase = True

    End If

    If Len(Txt) > 0 Then

        rv = Txt

        'looping over an array of patterns

        For Each p In Array("^(?[0-9.]+)?", n & "(S*)?(s+)?")

            With regEx

                .Pattern = p

                rv = .Replace(rv, "")

            End With

        Next p

    End If

    test = Application.Trim(rv)

End Function