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

#### Mark Roberts

##### Board Regular
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>

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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))

Here's a possibility:

Excel 2007
ABCD
1367921921
252052
315603010310
Sheet4
Cell Formulas
RangeFormula
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

B1, formula copy across and fill down :

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

Regards

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

Replies
5
Views
139
Replies
12
Views
565
Replies
4
Views
215
Replies
4
Views
205
Replies
11
Views
329

1,202,984
Messages
6,052,913
Members
444,612
Latest member
FajnaAli

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back