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?

2

1 Answer 1

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
4
  • Why xlPart instead of xlWhole?
    – nixda
    Oct 9, 2013 at 7:05
  • Seems to be my mistake. Updated post. Oct 9, 2013 at 8:01
  • I dont have a Developer tab i have a Macro button but i don't see anything about Visual Basic Oct 9, 2013 at 20:31
  • i figured it out and it worked thank you very much for your help! Oct 9, 2013 at 20:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .