Converting multiple data codes to numbers

  • Thread starter Thread starter Natasha
  • Start date Start date
N

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.
 
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:D,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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I add letters to page numbers in Excel. 2
Number generator and combination 1
Searching multiple data to show desired result 3
Conditional Formatting 3
The order of numbers mixed with letters 1
Data Entry - Write to next cell 3
Convert Numbers to Text 4
Adding one to the number from the previous cell with text... 8

Back
Top