Converting multiple data codes to numbers

[Natasha]

Hi there,

I have an extensive data set entered in excel. The data includes several
codes with number-letter combinations (e.g., 1a, 1b, 2f, etc). I need to
convert these codes to numerical values (e.g., 1a = 1). There are too many
to use the search / replace command. Is there a formula I can use to change
the codes more efficiently?

Thanks.

 

[Sheeloo]

Assuming your codes are in one column then you may use VLOOKUP...

1. Set up two columns first with current codes (1a,...) and second with
corresponding code you want (1,...), say in Col C and D
2. Now if you have your old codes in Col A then enter this in B1
=VLOOKUP(A1,C,2,False)
3. Copy the formula down

This will give you the new codes in Col B

4. You can copy Col B and PASTE SPECIAL->Values over Col A