CODEWITHSUNDEEP
excelvba
SteveG
SteveG

Reputation: 69

Replace function in VBA

I am trying to do some data clean up and could use some help with the replace function.

If I need to replace a cells value in excel from lets say "Paul the Optometrist Pa" to "Paul the Optometrist PA" I would just use something like this 

Replace(t, " Pa", " PA")

The problem with that is it also changes the letters in Paul to "PAul". Obviously I do not want that. Is there a way to keep it from changing anything in the string that isn't specifically the "PA" with nothing but spaces possible before and after it?

Upvotes: 1

Views: 774

Answers (1)

Tom
Tom

Reputation: 9878

This RegEx will match Pa in the string and replace it with PA. This can be easily updated to loop through your data set

Sub RegExExample()               
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = False
        .Pattern = "(Pa)\b"

        MsgBox .Replace("Paul the Optometrist Pa", "PA")
    End With
End Sub

Will output Paul the Optometrist PA

Upvotes: 1

Related Questions