Return last number, 2nd to last, 3rd to last in a number string. With variable lengths of numbers.

[Mark Roberts]

Hi,

Hope the title makes sense. I'll explain below what I mean. RIGHT, LEFT etc does not work for this (well a simple one doesn't).

In CELL A1 I have 367921. In CELL B1 I want the number 9 to show (3rd last). In CELL C1 I want the number 2 to show (2nd last) and in CELL D1 I want the number 1 to show (last number).

However in CELL A2 I have the numbers 52. So in CELL B2 I would want 0 as there is no third to last number. In CELL C2 I would want 5 and in CELL D2 I would want 2 to show.

Just to add a bit more confusion if there is a zero in any of the final three numbers I would want to show this as a number 10. e.g. If I have the string 156030 in CELL A3 I would want 10 in CELL B3, 3 in CELL C3 and 10 in CELL D3.

I have typed this in manually below. Any help greatly appreciated as always.

367921	9	2	1
52	0	5	2
156030	10	3	10

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>

 

[JimM]

Not pretty but

B1
=IF(ISERROR(MID(A1,LEN(A1)-2,1)),0,IF(MID(A1,LEN(A1)-2,1)="0",10,MID(A1,LEN(A1)-2,1)))

C1
=IF(ISERROR(MID(A1,LEN(A1)-1,1)),0,IF(MID(A1,LEN(A1)-1,1)="0",10,MID(A1,LEN(A1)-1,1)))

D1
=IF(RIGHT(A1,1)="0",10,RIGHT(A1,1))

 

[Firefly2012]

Here's a possibility:

Excel 2007
	A	B	C	D
1	367921	9	2	1
2	52	0	5	2
3	156030	10	3	10
Sheet4

Cell Formulas
Range	Formula
B1	=HEX2DEC(MID("000"&SUBSTITUTE($A1,"0","A"),LEN("000"&$A1)+1-COLUMNS(B$1:$D$1),1))

formula in B1 is copied down/across

 

[bosco_yip]

B1, formula copy across and fill down :

=IFERROR(TEXT(MID($A1,LEN($A1)+COLUMN(A1)-3,1),"[=0]10"),"0")

Regards

 

[Mark Roberts]

Excellent guys. Can't thank you enough, that has saved me a hell of a lot of time.