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

[Kayferz]

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

 

[63falcondude]

You can have your table as a lookup table and use a formula such as this below:

Excel 2010
	A	B	C	D	E
1	L1B	1		L1B	1
2	L1P	2		L1A	3
3	L1A	3		L1P	2
4	L2B	4
5	L2P	5
Sheet1

Cell Formulas
Range	Formula
E1	=INDEX($B$1:$B$5,MATCH(D1,$A$1:$A$5,0))

 

[MattH1]

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

=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.

 

[Marcílio_Lobão]

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

Cell	Formula
E1	=VLOOKUP(D1, $A$1:$B$5, 2, FALSE)

<tbody>
</tbody>

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

 

[Kayferz]

Hi

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

 

[Kayferz]

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.

 

[63falcondude]

Glad to help!