vba to replace old words with new words in a column

[tonywatsonhelp]

Hi Everyone,

So I'm stuck with a problem,

I want to replace old words with new words in a column,

Now the problem i have is that If I try replacing "Bill" for example it not only replaces Bill, but also replaces those letters in any other word like "unbilled"

is there a way for me to be able to only replace the word if it is just the word?

so Bill to Sue would be:

so for example cell N10 might say "We told Bill, he should Be Billing Bob, but,Bill,Is Unbillable" to "We told Sue, he should Be Billing Bob, but,Sue,Is Unbillable"

I tried to show what I'm up against, Bill might be surrounded by either a space or a comer on either or both sides.

Currently i was a simple find and replace as below, but any way to do this will be fine

Range("N4:N1000").Replace What:=Oldvalue3, Replacement:=Newvalue3, LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False _
        , ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

please help if you can

thanks

Tony

 

[JoeMo]

Instead of replacing just "Bill", you could replace " Bill " to ensure that only occurrences of Bill or bill surrounded by spaces are replaced. And the replacement would be " Sue " surrounded by spaces too. You could also replace "Bill " to catch occurrences at the start of a string, and " Bill" for occurrences at the end of a string.

 

[tonywatsonhelp]

Hi Joe,
Great, I'll do that, thanks for the good idea
Thanks
Tony

 

[Joe4]

Instead of replacing just "Bill", you could replace " Bill " to ensure that only occurrences of Bill or bill surrounded by spaces are replaced. And the replacement would be " Sue " surrounded by spaces too. You could also replace "Bill " to catch occurrences at the start of a string, and " Bill" for occurrences at the end of a string.

The issue with that is it will miss all instance where Bill or Sue is the first or last word of a sentence, i.e.
"Bill works here."
"Have you talked to Sue?"

 

[Fluff]

Also something like this "We told Bill, he should Be Billing Bob, but,Bill,Is Unbillable" will not change as you have a comma & not a space after Bill (and also a comma before the 2nd Bill)

 

[JoeMo]

The issue with that is it will miss all instance where Bill or Sue is the first or last word of a sentence, i.e.
"Bill works here."
"Have you talked to Sue?"

Thought I caught that in my post which read in part: "You could also replace "Bill " to catch occurrences at the start of a string, and " Bill" for occurrences at the end of a string."

Also something like this "We told Bill, he should Be Billing Bob, but,Bill,Is Unbillable" will not change as you have a comma & not a space after Bill (and also a comma before the 2nd Bill)

Yes, but one can always include these rarer occurrences by specifically replacing ",Bill," and so on.

 

[Joe4]

Thought I caught that in my post which read in part: "You could also replace "Bill " to catch occurrences at the start of a string, and " Bill" for occurrences at the end of a string."

Yes, but one can always include these rarer occurrences by specifically replacing ",Bill," and so on.

Yes, sorry I missed that.

But typically, I have found if you have a lot of data, and more than a few names to check, it is difficult to do this with any sort of confidence that you have caught all instances, and nothing but the instances you want to catch. As Fluff pointed out, there are "exceptions" that will cause issues, and it is very difficult to anticipate them all (sometimes creating all the necessary rules can be more trouble than they are worth).

 

[JoeMo]

Yes, sorry I missed that.

But typically, I have found if you have a lot of data, and more than a few names to check, it is difficult to do this with any sort of confidence that you have caught all instances, and nothing but the instances you want to catch. As Fluff pointed out, there are "exceptions" that will cause issues, and it is very difficult to anticipate them all (sometimes creating all the necessary rules can be more trouble than they are worth).

There's no free lunch with what I suggested, but is there an alternative?

 

[Joe4]

There's no free lunch with what I suggested, but is there an alternative?

Not too many good ones. My main point was not to criticize your approach, it was just to let you user know that they should not have the expectation that it will 100% correct, and it may still require some manual intervention.

If it were me, I would probably try to do some sort of hybrid approach, like replacing all the certain ones (i.e. with spaces on both sides, like you suggested, maybe "space + name + period"), and then highlight all the others using Conditional Formatting to alert me to check those manually.

 

[tonywatsonhelp]

Hi Guys,
You have very valid points, fortunately for me I only had about 6 different rules, heres what i did,

'first replace any where "Bill" is the only text
Range("N4:N1000").Replace What:=Oldvalue3, Replacement:=Newvalue3, LookAt _
        :=xlwhole SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False _
        , ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'then any with a space after them 
Range("N4:N1000").Replace What:=Oldvalue3 & " ", Replacement:=Newvalue3 & " ", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False _
        , ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'then any with a space in front (getting any last words)
Range("N4:N1000").Replace What:=" " & Oldvalue3, Replacement:=" " & Newvalue3, LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False _
        , ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'then any with a Comer after them 
Range("N4:N1000").Replace What:=Oldvalue3 & ",", Replacement:=Newvalue3 & ",", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False _
        , ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'then any with a comer in front
Range("N4:N1000").Replace What:="," & Oldvalue3, Replacement:="," & Newvalue3, LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False _
        , ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

This has worked perfectly, because I know how the cells are going to be formatted I know the above are the only ways "Bill" can apear so it works, I agree if you are not 100% sure of your data this could miss instances like "Bill/Mary" but I dont have to worry about that so all good.

Thank you both for your help.

Thanks

Tony