Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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
share|improve this answer
    
Great, even better. Thanks –  user65678 Feb 1 '11 at 21:15
add comment

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.

share|improve this answer
    
Great, not particularly elegant, but it works just fine, Thanks –  user65678 Feb 1 '11 at 19:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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