Use a Helper sheet, format A-C columns as text:
A1-A10: 1-0
B1-B10: A-J
C1-C10: K-T

In your example there was no place for digit 0!

Your amount is, say, in A1, then in B1:
=LEFT(A1,LEN(A1)-1) & VLOOKUP(RIGHT(A1,1),Helper!A1:C10,IF(A10,2,3),FAL SE)


"bramruis via OfficeKB.com" wrote:

My cells in a column are filled with amounts for
which I have to change the last digit into a letter.
E.g. if last digit is 1 and the amount is positive it should be changed into
an A if it's negative it should be changed in a J
If the last digit is 2 and the amount is positive it should be changed into a
B, if it's negative it should be changed into a K.
If the last digit is 3 and the amount is positive it should be changed into a
C, it it's negative it should be changed into a L.
And so on.

I tried to use this formula but I'm hitting the 7 nested functions ceiling
because I need to change the last digits 0-9 both positive and negative.
Example: =IF(J40,REPLACE(J4,LEN(J4),1,IF(RIGHT(J4,1)="1"," A",IF(RIGHT(J4,1)
="2","B",IF(RIGHT(J4,1)="3","C",IF(RIGHT(J4,1)="4" ,"E"))))))

Is there any other way you can think of?
Thanks.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200510/1