Replace last number using provided matrix

[richmcgill]

I need to apply the matrix below to a group of numbers
The matrix replaces the last number of the whole number with the specified character.
A negative number
-2505.25 would be 2505.2N
A positive number
31066.83 would be 31066.8C

Make sense?

Final Digit of Number	Positive Replacement Character	Negative Replacement Character
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
0	{	}

 

[dreid1011]

Quick question.
If I wanted to use the same (the new one you resorted) matrix and apply replacing the last digit of the Date number in the spreadsheet keeping it three digits what would that look like?
001 would be 00A
-012 would be 01K
-005 would be 00N

Loan Number	Code	Reason	Date
123456	5	AAAA	001
234567	6	BBBB	-012
345678	8	CCCC	008
456789	7	EEEE	-005

You can try this for the 3 digit problem:

Book1
	F	G	H
1	001		00A
2	-012		01K
3	008		00H
4	-005		00N
Sheet4

Cell Formulas
Range	Formula
H1:H4	H1	=LEFT(TEXT(ABS(F1),"000"),LEN(TEXT(ABS(F1),"000"))-1)&LOOKUP(RIGHT(F1,1)*1,$N$2:$N$11,IF((F1)*1>=0,$O$2:$O$11,$P$2:$P$11))

 

[dreid1011]

And I have this working for the original problem, though I rearranged the matrix slightly from your original orientation. I put line 0 above instead of at the end because LOOKUP needs to be in ascending order. Anyway, for this version to work, the balance values should be converted to text to properly recognize the trailing 0. Maybe @felixstraube can adjust his formula to account for the tens place 0 too, but for the moment, this should work if you convert your balances to text.

Book1
	I	J	K	L	M	N	O	P
1	-50756.02	50756.0K				Final Digit of Number	Positive Replacement Character	Negative Replacement Character
2	27861.48	27861.4H				0	{	}
3	40399.59	40399.5I				1	A	J
4	33034.48	33034.4H				2	B	K
5	75701.86	75701.8F				3	C	L
6	55527.80	55527.8{				4	D	M
7	-91358.80	91358.8}				5	E	N
8	6383.60	6383.6{				6	F	O
9	-56943.38	56943.3Q				7	G	P
10	-97315.69	97315.6R				8	H	Q
11	-10585.67	10585.6P				9	I	R
12	34849.25	34849.2E
13	15970.51	15970.5A
14	4856.32	4856.3B
15	-78592.57	78592.5P
16	-52665.77	52665.7P
17	-15140.82	15140.8K
18	93981.01	93981.0A
19	-55953.57	55953.5P
20	64066.68	64066.6H
21	23742.54	23742.5D
22	61665.64	61665.6D
23	-39963.09	39963.0R
24	-47585.35	47585.3N
25	4928.59	4928.5I
26	-62347.23	62347.2L
27	97990.58	97990.5H
28	-35061.06	35061.0O
29	97556.41	97556.4A
30	21993.70	21993.7{
Sheet4

Cell Formulas
Range	Formula
I1:I30	I1	=TEXT(A1:A30,"0.00")
J1:J30	J1	=LEFT(TEXT(ABS(I1),"0.00"),LEN(TEXT(ABS(I1),"0.00"))-1)&LOOKUP(RIGHT(I1,1)*1,$N$2:$N$11,IF((I1)*1>=0,$O$2:$O$11,$P$2:$P$11))
Dynamic array formulas.

 

[felixstraube]

I ran into a problem with the first formula, it does not recognize when there is a zero at the end of the number and the negative it added the special character at the end of the negative number not replacing the last digit.

The new option works much better and so far I cannot find any flaws.

Thank you so much for your help!

You are welcome. Thanks for the feedback!

 

[felixstraube]

Are you sure? What happens with values of .0#?

Book1
	A	B	C	D
1	-50756.02	50756.02K		50756Q
Sheet4

Cell Formulas
Range	Formula
B1	B1	=LEFT(ABS(A1),LEN(A1)-1)&LOOKUP(RIGHT(A1,1)*1,$N$2:$N$11,IF(A1>=0,$O$2:$O$11,$P$2:$P$11))
D1	D1	=TRUNC(ABS(A1), 1)&INDEX($Q$2:$R$11, 1+MOD(ABS(A1)*100, 10), 1+(A1<0))

See col D for felix's formula. It got the last digit right for the character, but then it removed the .0 before the last digit. Is that the desired outcome? Should it be 50756.0Q?

You are right, for the .0 numbers the formula didn't work properly.
Here is the corrected formula:

=TEXT(TRUNC(ABS(E2), 1), "0.0")&INDEX($J$2:$K$11, 1+MOD(ABS(E2)*100, 10), 1+(E2<0))

 

[richmcgill]

Thank you!!! This made my work a whole lot easier.

 

[felixstraube]

Happy to help. Thanks for the feedback!