2

I have a large amount of numbers in an Excel spreadsheet that I want to replace with text. It looks something like this:

33
33
33
33
22
21
22
29
49
95
43
23
45
12
11
22

I want all of the 33's to be replaced with "Clothing", all of the 22's to be replaced with "House", etc. I have to do this on a day-to-day basis, so I don't want to use the lookup and replace button and go through 43 numbers, replacing them with custom strings.

How can this be automated?

1

You can use vba macro (Developer tab -> Visual Basic -> Sheet1(your sheet where you need replacements) and paste following code to visual basic editor, then click "execute")

Sub ReplaceNumbers()
With Range("A1:A20000")
    .Replace "33", "Clothing", xlWhole, , True
    .Replace "22", "House", xlWhole, , True
End With
End Sub

That is the most hard-coded solution, i'm sure there is a lot of things that can be improved, thought it works.
If you want to add replacement rule, just add line

.Replace "Number-to-replace", "Replacement", xlWhole, , True
  • Why xlPart instead of xlWhole? – nixda Oct 9 '13 at 7:05
  • Seems to be my mistake. Updated post. – Quadrat137 Oct 9 '13 at 8:01
  • I dont have a Developer tab i have a Macro button but i don't see anything about Visual Basic – Kevin Kruger Oct 9 '13 at 20:31
  • i figured it out and it worked thank you very much for your help! – Kevin Kruger Oct 9 '13 at 20:50

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.