Assistance with RegEx Pattern Find and Replace

[DBezel]

Hi all.
Seeking some assistance in coding a worksheet to identify a pattern and replace a portion with text.
In column G, I have a list of team names which read like: White, John AR IC Westman Supervisor Team. I am wanting to remove AR which is a variable state abbr and IC for independent contractor and replace both with VM. So result would be White, John VM Westman Supervisor Team. Westman is the name of a contractor and we have about 7 different variable contractor names that I'm prepared to loop in. Last Name, First Name will also be different combinations.

This is what I have so far but it isn't working out. Trying to figure out if this will work somehow or if I will have to loop for each Contractor name.

Sub TestRegEx()
Dim regex As Object, regexMatches As Object
Dim r As Range, rC As Range
Dim sContractors As String, sContractor As String

sVendors = "Contractor1|Contractor2|Contractor3|Contractor4|Contractor5|Contractor6|Contractor7"


Set r = Range("G2", Cells(Rows.Count, "G").End(xlUp))

Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "^(\S+, \S+ ).+?( sVendors .+)$"
regex.IgnoreCase = True

For Each rC In r

Set regexMatches = regex.Execute(rC.Value)
If regexMatches.Count > 0 Then
rC.Value = regex.Replace(rC.Value, "$1VM$2")

End If
Next rC

End Sub

Any help appreciated. Thanks!

 

[mohadin]

Hi perhaps

Sub TestRegEx()
    Dim regex As Object
    Dim r As Range, rC As Range
    Dim sVendors
    sVendors = "Contractor1|Contractor2|Contractor3|Contractor4|Contractor5|Contractor6|Contractor7"
    Set r = Range("G2", Cells(Rows.Count, "G").End(xlUp))
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = sVendors
    regex.IgnoreCase = True
    For Each rC In r
        If regex.Test(rC) Then
            rC.Value = regex.Replace(rC.Value, "VM")
        End If
    Next rC
End Sub

 

[Domenic]

Try matching the following pattern . . .

regex.Pattern = "^(.+)(?:AR\sIC)(\s(?:" & sVendors & ").+)$"

Hope this helps!

 

[DBezel]

Appreciate you both! Both helpful in aiding me to understand RegEx patterns. Used a combo of both to come up with the below which worked.


Sub TeamSupervisorUpdate()

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Dim a, i As Long
Dim r As Range, rC As Range
Dim regex As Object, regexMatches As Object
Dim sVendors As String

sVendors = "Contractor1|Contractor2|Contractor3|Contractor4|Contractor5"

Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "^(.+)(?:\S\S\sIC)(\s(?:" & "Contractor1|Contractor2|Contractor3|Contractor4|Contractor5" & ").+)$"

Set r = Range("K2", Cells(Rows.Count, "K").End(xlUp))

For Each rC In r
Set regexMatches = regex.Execute(rC.Value)

If regexMatches.Count > 0 Then
rC.Value = regex.Replace(rC.Value, "$1VM$2")
End If
Next rC

'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[mohadin]

So glad you sorted out
Thank you for the feedback
Be happy and safe