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]

What does your data look like? And are you looking for a formula option or VBA?

 

[richmcgill]

What does your data look like? And are you looking for a formula option or VBA?

Looking for a formula.
Loan number

What does your data look like? And are you looking for a formula option or VBA?

Loan Number	Code	Reason	Date	Balance	Coulmn E should be
123456	5	AAAA	001	-25789.56	25789.5O
234567	6	BBBB	002	55789.45	55789.4E
345678	8	CCCC	008	-8886.78	8886.7Q
456789	7	EEEE	005	45879.52	45879.5B

 

[dreid1011]

Give this a try:

Book1
	A	B	C	D	E	F	G
1	Loan Number	Code	Reason	Date	Balance	Coulmn E should be	Formula
2	123456	5	AAAA	1	-25789.56	25789.5O	25789.56O
3	234567	6	BBBB	2	55789.45	55789.4E	55789.4E
4	345678	8	CCCC	8	-8886.78	8886.7Q	8886.78Q
5	456789	7	EEEE	5	45879.52	45879.5B	45879.5B
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=LEFT(ABS(E2),LEN(E2)-1)&LOOKUP(RIGHT(E2,1)*1,Sheet2!$A$2:$A$11,IF(E2>=0,Sheet2!$B$2:$B$11,Sheet2!$C$2:$C$11))

Book1
	A	B	C
1	Final Digit of Number	Positive Replacement Character	Negative Replacement Character
2	1	A	J
3	2	B	K
4	3	C	L
5	4	D	M
6	5	E	N
7	6	F	O
8	7	G	P
9	8	H	Q
10	9	I	R
11	0	{	}
Sheet2

And just a note, because of your comment in col F, a formula cannot change the value in a cell. So, if you are wanting the results to show in col E where the balance is already, you will need to use VBA. A formula will need to be in its own column.

 

[felixstraube]

Another option (changing the order of the character table):

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Loan Number	Code	Reason	Date	Balance	Coulmn E should be	Formula
2	123456	5	AAAA	1	-25789.56	25789.5O	25789.5O			{	}
3	234567	6	BBBB	2	55789.45	55789.4E	55789.4E			A	J
4	345678	8	CCCC	8	-8886.78	8886.7Q	8886.7Q			B	K
5	456789	7	EEEE	5	45879.52	45879.5B	45879.5B			C	L
6										D	M
7										E	N
8										F	O
9										G	P
10										H	Q
11										I	R
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=TRUNC(ABS(E2), 1)&INDEX($J$2:$K$11, 1+MOD(ABS(E2)*100, 10), 1+(E2<0))

 

[richmcgill]

This works perfectly! Thank you so much!!!

 

[dreid1011]

This works perfectly! Thank you so much!!!

You're welcome, and happy to help.

 

[richmcgill]

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!

 

[dreid1011]

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!

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?

 

[richmcgill]

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