On Thu, 27 Oct 2005 13:14:07 GMT, "bramruis via OfficeKB.com" <u15176@uwe
wrote:

Yes, sorry. You're right.
Here are the values that I need:

Last digit: Positive value: Negative value:

0 { }
1 A J
2 B K
3 C L
4 D M
5 E N
6 F O
7 G P
8 H Q
9 I R

Thanks,

Bram.

Ron Rosenfeld wrote:
My cells in a column are filled with amounts for
which I have to change the last digit into a letter.
[quoted text clipped - 13 lines]
Is there any other way you can think of?
Thanks.

You're description is inconsistent since it seems as if you want to convert
digits 0-9, yet if you have 1 = A or J depending on positive or negative, it's
not clear what you want to do if the last digit is zero.

So I changed your specifications so that 0 -- A or K; 1 -- B or L; etc.

You can do that with the formula:

=LEFT(A1,LEN(A1)-1)&CHAR(RIGHT(A1,1)+65+10*(A1<0))

--ron

Well, have to make zero a special case, then.

You can use this formula:

=LEFT(A1,LEN(A1)-1)&IF(MOD(A1,10)<0,CHAR(
RIGHT(A1,1)+64+9*(A1<0)),CHAR(123+2*(A1<0)))


--ron