Mass Find And Replace

[boxboy30]

How do I find and replace many things at once....example:

ShawnH
ShawnHa
shawnHarr
Shawn.
ShawnT

I want to find all of these and replace with Shawn...

Cells.Replace What:="ShawnH" & "ShawnHa" & "ShawnHarr" & "Shawn." & "ShawnT", Replacement:="Shawn", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

this isnt' working though! What am I missing?

 

[poolhall]

"ShawnH" & "ShawnHa" & "ShawnHarr" & "Shawn." & "ShawnT" results in the string "ShawnHShawnHaShawnHarrShawn.ShawnT".

You need to do Cells.Replace several times for each searched word.

 

[VoG]

Maybe

Cells.Replace What:="Shawn*", Replacement:="Shawn", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[boxboy30]

that worked...so in the future I can just add an "*" and that will do the trick?

thanks a bunch

 

[VoG]

Pretty much it will work with the *. You will have some difficulty if you wanted to exclude Shawn.Buddhist for example.

 

[boxboy30]

why is that because of the "."?

 

[VoG]

No. What I meant is that using Shawn* will replace anything beginning with Shawn with Shawn. You might not always want that.

 

[boxboy30]

Gotcha!

Thanks,