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

Ron,

What I found out now is that when an amount is 66.60 Excel will cut of the
trailing zero and the result of your formula will be 66.F
I should be 66.6{
I could change the format of the cell to text but then the negative amounts
are no longer recognized.

Any ideas?

Thanks,

Bram.

Ron Rosenfeld wrote:
Thanks Dave and Ron.
Your formulas both work great!!

Bram.

You're welcome. Thanks for the feedback.

--ron

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

On Fri, 11 Nov 2005 10:21:57 GMT, "bramruis via OfficeKB.com" <u15176@uwe
wrote:

Ron,

What I found out now is that when an amount is 66.60 Excel will cut of the
trailing zero and the result of your formula will be 66.F
I should be 66.6{
I could change the format of the cell to text but then the negative amounts
are no longer recognized.

Any ideas?

Thanks,

Bram.

I did not realize you would be dealing with decimals.

That can be handled.

But how is the cell formatted? Is the formatting fixed or variable?

In order to have a trailing zero after the decimal, it must be formatted with
some known number of decimal places.

If the cell is formatted with two trailing decimal places,
and the number stored is 91.995,
and the number displayed is 92.00,
what do you want the translation to be?

In other words, do you want the translation to be based on the number stored,
or the number displayed?


--ron

Ron,

The values in the cells are (currency) amounts with 2 decimals. The problem I
think lies in the fact that although Excel is showing e.g. 99.20 in the cell
the actual value shown in the Formula bar is 99.2 and that's why the zero is
not converted but the 2 is.
I hope you can help me out on this one.

Bram.


Ron Rosenfeld wrote:
Ron,

[quoted text clipped - 9 lines]

Bram.

I did not realize you would be dealing with decimals.

That can be handled.

But how is the cell formatted? Is the formatting fixed or variable?

In order to have a trailing zero after the decimal, it must be formatted with
some known number of decimal places.

If the cell is formatted with two trailing decimal places,
and the number stored is 91.995,
and the number displayed is 92.00,
what do you want the translation to be?

In other words, do you want the translation to be based on the number stored,
or the number displayed?

--ron

--
Message posted via http://www.officekb.com

Use TEXT(cell,format) to deal with the formatted result in a formula.

Jerry

bramruis via OfficeKB.com wrote:

Ron,

The values in the cells are (currency) amounts with 2 decimals. The problem I
think lies in the fact that although Excel is showing e.g. 99.20 in the cell
the actual value shown in the Formula bar is 99.2 and that's why the zero is
not converted but the 2 is.
I hope you can help me out on this one.

Bram.


Ron Rosenfeld wrote:

Ron,


[quoted text clipped - 9 lines]

Bram.

I did not realize you would be dealing with decimals.

That can be handled.

But how is the cell formatted? Is the formatting fixed or variable?

In order to have a trailing zero after the decimal, it must be formatted with
some known number of decimal places.

If the cell is formatted with two trailing decimal places,
and the number stored is 91.995,
and the number displayed is 92.00,
what do you want the translation to be?

In other words, do you want the translation to be based on the number stored,
or the number displayed?

--ron

Just a matter of using the formatted result in my formula, rather than the
numeric amount; and also handling text representations of the last digit
instead of numeric representations.

In the formula below, you may want to change the format string "0.00" in the
TEXT function if you want the result displayed in some other format.

For example $1,234,567.01 will be converted to 1234567.0A.

If what you want is $1,234,567.0A, then change the format string to:

"$#,##0.00"

======================================
=LEFT(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00"))-1)&
IF(RIGHT(TEXT(A1,"0.00"),1)<"0",CHAR(RIGHT(
TEXT(A1,"0.00"),1)+64+9*(SIGN(A1)=-1)),
CHAR(123+2*(SIGN(A1)=-1)))
=======================================




On Mon, 14 Nov 2005 11:09:50 GMT, "bramruis via OfficeKB.com" <u15176@uwe
wrote:

Ron,

The values in the cells are (currency) amounts with 2 decimals. The problem I
think lies in the fact that although Excel is showing e.g. 99.20 in the cell
the actual value shown in the Formula bar is 99.2 and that's why the zero is
not converted but the 2 is.
I hope you can help me out on this one.

Bram.


Ron Rosenfeld wrote:
Ron,

[quoted text clipped - 9 lines]

Bram.

I did not realize you would be dealing with decimals.

That can be handled.

But how is the cell formatted? Is the formatting fixed or variable?

In order to have a trailing zero after the decimal, it must be formatted with
some known number of decimal places.

If the cell is formatted with two trailing decimal places,
and the number stored is 91.995,
and the number displayed is 92.00,
what do you want the translation to be?

In other words, do you want the translation to be based on the number stored,
or the number displayed?

--ron

--ron

Ron,

This works great.
Thanks again for your help.

Bram.

Ron Rosenfeld wrote:
Just a matter of using the formatted result in my formula, rather than the
numeric amount; and also handling text representations of the last digit
instead of numeric representations.

In the formula below, you may want to change the format string "0.00" in the
TEXT function if you want the result displayed in some other format.

For example $1,234,567.01 will be converted to 1234567.0A.

If what you want is $1,234,567.0A, then change the format string to:

"$#,##0.00"

======================================
=LEFT(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00"))-1)&
IF(RIGHT(TEXT(A1,"0.00"),1)<"0",CHAR(RIGHT(
TEXT(A1,"0.00"),1)+64+9*(SIGN(A1)=-1)),
CHAR(123+2*(SIGN(A1)=-1)))
=======================================

Ron,

[quoted text clipped - 30 lines]

--ron

--ron

--
Message posted via http://www.officekb.com

On Mon, 14 Nov 2005 13:33:46 GMT, "bramruis via OfficeKB.com" <u15176@uwe
wrote:

Ron,

This works great.
Thanks again for your help.


You're welcome. Glad it's working.


Bram.

Ron Rosenfeld wrote:
Just a matter of using the formatted result in my formula, rather than the
numeric amount; and also handling text representations of the last digit
instead of numeric representations.

In the formula below, you may want to change the format string "0.00" in the
TEXT function if you want the result displayed in some other format.

For example $1,234,567.01 will be converted to 1234567.0A.

If what you want is $1,234,567.0A, then change the format string to:

"$#,##0.00"

======================================
=LEFT(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00"))-1)&
IF(RIGHT(TEXT(A1,"0.00"),1)<"0",CHAR(RIGHT(
TEXT(A1,"0.00"),1)+64+9*(SIGN(A1)=-1)),
CHAR(123+2*(SIGN(A1)=-1)))
=======================================

Ron,

[quoted text clipped - 30 lines]

--ron

--ron

--ron