How to replace the last digit in a cell with a letter

[bramruis via OfficeKB.com]

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(J4>0,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.

 

[Guest]

Something like:

=IF(J4<0,LEFT(J4,LEN(J4)-1)&VLOOKUP(RIGHT(J4,1)*1,$N$2:$P$10,3,0),IF(J4>0,LEFT(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 P210 J - R

HTH

 

[Guest]

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(A1>0,2,3),FALSE)

 

[Ron Rosenfeld]

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(J4>0,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

 

[bramruis via OfficeKB.com]

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.

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

 

[Dave Peterson]

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)

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.

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

 

[Ron Rosenfeld]

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.

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

 

[bramruis via OfficeKB.com]

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

Bram.

Yes, sorry. You're right.
Here are the values that I need:

[quoted text clipped - 33 lines]
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

 

[Ron Rosenfeld]

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

Bram.

You're welcome. Thanks for the feedback.


--ron

 

[bramruis via OfficeKB.com]

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]

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

 

[bramruis via OfficeKB.com]

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,

[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

 

[Jerry W. Lewis]

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

Jerry

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,

[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 Rosenfeld]

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,

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,

[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

 

[bramruis via OfficeKB.com]

Ron,

This works great.
Thanks again for your help.

Bram.

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

Ron,

This works great.
Thanks again for your help.

You're welcome. Glad it's working.

Bram.

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