Is there a way to convert info in a column to different values in the next column

Kayferz

New Member
 L1B 1 L1P 2 L1A 3 L2B 4 L2P 5

<tbody>
</tbody>

Hi everyone

I am attempting to create a formula to convert information in the first column to a numerical value in the second column. Basically I have 5 different codes and each one needs to be converted into a numerical value in the next column. For example L1B becomes '1' L2P becomes '5'. Is there a way to do this?

Many thanks
Kayferz

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

63falcondude

Well-known Member
You can have your table as a lookup table and use a formula such as this below:

Excel 2010
ABCDE
1L1B1L1B1
2L1P2L1A3
3L1A3L1P2
4L2B4
5L2P5
Sheet1
Cell Formulas
RangeFormula
E1=INDEX(\$B\$1:\$B\$5,MATCH(D1,\$A\$1:\$A\$5,0))

MattH1

Board Regular
Place this code in the second column (assuming your first set of data is in Column A, this would be Column B):

Code:
``=IF(\$A1="L1B",1,IF(\$A1="L1P",2,IF(\$A1="L1A",3,IF(\$A1="L2B",4,IF(\$A1="L2P",5,)))))``

This code can be dragged down for the rest of the column.

Last edited:

Marcílio_Lobão

Well-known Member
Kayferz, Good evening.

As you can see from the above suggestions, Excel is a very powerful and flexible tool.

Greetings from Brazil to 63falcondude and MattH1.

Using the same 63falcondude's layout, put this formula in E1 and drag it down.

Worksheet Formulas
CellFormula
E1=VLOOKUP(D1, \$A\$1:\$B\$5, 2, FALSE)

<tbody>
</tbody>

Is that what you want?
I hope it helps too.

Kayferz

New Member
Hi

Thank you so much for this. I followed your instructions and got this to work perfectly. Thank you.

Kayferz

New Member
Hi

Thank you for this. I was trying to do something like this, but could not get it to work. I was closing the brackets too early. Your code example was really helpful and worked perfectly for me. Thank you.

Replies
0
Views
171
Replies
2
Views
594
Replies
1
Views
97
Replies
4
Views
637
Replies
11
Views
2K

1,191,177
Messages
5,985,138
Members
439,941
Latest member
robertv13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back