converting numbers to letters

Posted by greylock on October 11, 2000 12:52 PM

i have 6 digit numbers that i want to convert to a
specified letter dependent on the last two digits.
for example: 12=c, 11=y, 10=p, etc... how can i do
this?
thanks, greylock

Posted by Loren on October 11, 2000 1:21 PM

Up to 7 nested, you can use If function.

Posted by greylock on October 11, 2000 1:26 PM

thanks loren ,but i have more than 7...
and i need to know how to use only the last
2 digits not the entire 6

Posted by Tim Francis-Wright on October 11, 2000 1:43 PM

To get the two last digits of, say, C2, you can
use =C2-TRUNC(C2,-2)

As for the conversion, one way would be to
use a lookup table, say in E1:F100
1 m
2 r
3 e
4 x
5 c
6 e
7 l
etc.

Then the letter equivalent for C2 would be
=vlookup(C2-TRUNC(C2,-2),$e$1:$f$100,2)

You can do this without a lookup by using a CHOOSE
function, but then you'll need to have (a)
no more than 30 choices that (b) are consecutive.

There's certainly an ugly concatenation that
does this relatively easily:

Say C3 = C2-TRUNC(C2,-2)
Then D3 can be
=IF(C3=12,"c","")&IF(D3=11,"y","")& ...

[or, you can define a worksheet name
while you're in C3 so EG = C2 - TRUNC(C2,-2);
then C3 can be =IF(EG=12,"c","")& etc.]

Posted by greylock on October 11, 2000 3:30 PM

Thanks Tim Francis-Wright

it works, i used the ugly concatenation method,
but it works. do you have any ideas on my other
problem posted on the message board?