Replace function in VBA

Question

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?

Tom · Accepted Answer · 2017-08-17 16:20:34Z

1

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

edited Aug 17, 2017 at 16:20

answered Aug 17, 2017 at 15:45

Tom

9,9084 gold badges33 silver badges51 bronze badges

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

5 Comments

Greedo Over a year ago

I don't quite follow this logic; since the string "Paul the Optometrist Pa" contains Paul and Pa with the same case structure. So surely case sensitivity is not relevant?

CLR Over a year ago

That's still going to produce PAul the Optometrist PA. OP is likely going to need to use regex with word borders to do this (I know what, I just don't know how)

Greedo Over a year ago

@CLR Actually it works fine for me, it's just the reasoning is incorrect. ?Replace("Paul the Optometrist Pa", " Pa", " PA", compare:=vbBinaryCompare) prints Paul the Optometrist PA

SteveG Over a year ago

Yea it would only because my example was bad. There is no space in front of Paul which is why it won't change it to PAul. if the value was "hello Paul Pa" it would still give me the same problem.

SteveG Over a year ago

@Tom Yep that is the winner! Thanks for the help.

Collectives™ on Stack Overflow

Replace function in VBA

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related