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

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

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

On Wed, 26 Oct 2005 08:24:44 GMT, "bramruis via OfficeKB.com" <u15176@uwe
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.

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

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


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

I'd create another worksheet (I used sheet2) and put that table in columns A:C.

Then with the value to convert in A1 (of the first sheet):

=LEFT(A1,LEN(A1)-1)&VLOOKUP(--RIGHT(A1),Sheet2!A:C,IF(A1=0,2,3),FALSE)



"bramruis via OfficeKB.com" 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

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

--

Dave Peterson

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

Thanks Dave and Ron.
Your formulas both work great!!

Bram.

Ron Rosenfeld wrote:
Yes, sorry. You're right.
Here are the values that I need:
[quoted text clipped - 33 lines]

--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


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

On Wed, 02 Nov 2005 08:43:35 GMT, "bramruis via OfficeKB.com" <u15176@uwe
wrote:

Thanks Dave and Ron.
Your formulas both work great!!

Bram.

You're welcome. Thanks for the feedback.


--ron