Excel VBA - Multiple regex pattern deletion

Question

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.

You can pass in an array of patterns along with the string to be modified, or define an array within your function and then loop over it and perform the replacements. — Tim Williams
– Tim Williams, Commented Nov 14, 2018 at 16:10
Hi Tim, I saw your post: stackoverflow.com/questions/9672436/…, but was unsure how to adapt it to my particular needs based on the fact that I am deleting and keeping remainders. May I have an example? — rew
– rew, Commented Nov 14, 2018 at 16:17

Tim Williams · Accepted Answer · 2018-11-14 17:22:17Z

1

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

answered Nov 14, 2018 at 17:22

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rew Over a year ago

Boom! Thank you very kindly Tim.

Collectives™ on Stack Overflow

Excel VBA - Multiple regex pattern deletion

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related