Something like:

=IF(J4<0,LEFT(J4,LEN(J4)-1)&VLOOKUP(RIGHT(J4,1)*1,$N$2:$P$10,3,0),IF(J40,L EFT(J4,LEN(J4)-1)&VLOOKUP(RIGHT(J4,1)*1,$N$2:$P$10,2,0),""))

In column N2:N10 1 - 9
In column O2:O10 A - I
In column P2:P10 J - R

HTH

"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