1

I need to replace a large amount of numbers with words in an Excel spreadsheet. Eg:

1 = hello
12 = goodbye
4 = cat

etc. I can do it with the standard search and replace, but I have a large list to work through (about 240 number/word combos), so i figured I would use a macro.

I have this:

Sub findreplacer()
For Each mycell In Range("A1:A1000")
mycell.Replace What:="1", Replacement:="hello"
mycell.Replace What:="12", Replacement:="goodbye"
mycell.Replace What:="4", Replacement:="cat"
Next

End Sub

But it replaces the 1 in 12 so the cell reads hello2 instead of goodbye. How can I make it just affect cells that only contain the specific number, the way 'match entire cell contents' works?

Any help appreciated.

2 Answers 2

6

Changing the order is fine, so long as every value in the sheet is going to be replaced. If your sheet contained 11 though that wasn't going to be replaced then changing the order will still replace it with hello1.
To use match entire cell specify

,LookAt:=xlWhole

as another parameter to the Replace method. Replace is a valid method on a Range so your For loop is not necessary. You can instead specify

Range("A1:A1000").Replace What:="12", Replacement:="goodbye", LookAt:=xlWhole  
Range("A1:A1000").Replace What:="4", Replacement:="cat", LookAt:=xlWhole
0
2

I'm not sure if there's a better solution, but what that says in my mind is put them in reverse order. For instance:

Sub findreplacer()
For Each mycell In Range("A1:A1000")
mycell.Replace What:="12", Replacement:="goodbye"
mycell.Replace What:="4", Replacement:="cat"
mycell.Replace What:="1", Replacement:="hello"
Next

End Sub

In that case, 12 would be replaced by "goodbye" first, and when the line that replaces 1 with "hello" comes around, it would have already changed.

Again, there's probably a better solution, but I think this would work for a quick fix in the parameters you described.

1
  • Great, not particularly elegant, but it works just fine, Thanks
    – user65678
    Feb 1, 2011 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.