Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to replace the last digit in a cell with a letter
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying cell names | Excel Discussion (Misc queries) | |||
Replace null string with blank cell | Excel Discussion (Misc queries) | |||
Replace part of link address in a cell with a value(date) from ano | Excel Discussion (Misc queries) | |||
Append Text to Cell Values Using Replace | Excel Discussion (Misc queries) | |||
How do I add row total when cell contains both number and letter . | Excel Discussion (Misc queries) |