How do I substitute numbers for certain words?

[Pyrex238]

I have the monumental task of converting an extremely large database over to a new system. One of the problems I have is some of the data is in a code format, which I need to change to a standard database number.

For example column B contains several codes such as SIE, HDY, NOS, etc.. and I need to convert these to corresponding numbers such as 1, 2, 3. What is the best way to go about doing this? I'd like to make a spreadsheet that I can paste the data into, and it will automatically convert it over... the important thing is I need the data in the cells to physically change, instead of just using a conditional format to change their appearance (not sure if that's the best explanation).

I would REALLY appreciate anyones help and will be sure to bump your rep for anything you can offer.

[Special-K]

With your words in Sheet1 column B
Create a table in Sheet2, 2 columns

Please Login or Register  to view this content.

In Sheet1 cell C1
=VLOOKUP(B1,Sheet2!A$1:B$3,2,FALSE)
and copy it down the column