formula to extract the number from each cell

[Neokey]

Hi,

Can anyone help please.

I have a table where I need to extract just the numbers from a cell. The problem is that not all cells contain a number so the cells that do not contain numbers need to be ignored.

I have added an extract from my workbook below.

<tbody>
[TD="class: xl65"]Walton Vale 1977[/TD]

[TD="class: xl65"]UK HQ[/TD]

[TD="class: xl65"]Mr K Sewell of M&R Motor Co[/TD]

[TD="class: xl65"]Richard Tipper[/TD]

[TD="class: xl65"]East Ham 1338[/TD]

[TD="class: xl65"]Widnes 1293[/TD]

[TD="class: xl65"]Amersham 1878[/TD]

[TD="class: xl65"]Peterborough 1484[/TD]

[TD="class: xl65"]Letchworth 2361[/TD]

[TD="class: xl65"]Norwich Thorpe 1365[/TD]

[TD="class: xl65"]Hexham A470[/TD]

[TD="class: xl65"]Smethwick 1540[/TD]

[TD="class: xl65"]Manchester 1235[/TD]

[TD="class: xl65"]Stockport 1582[/TD]

[TD="class: xl65"]Swadlincote 1946[/TD]

[TD="class: xl65"]Abbie Cardie[/TD]

[TD="class: xl65"]Paul Dowty[/TD]

[TD="class: xl65"]Basildon 1156[/TD]

[TD="class: xl65"]Gloucester 2485
[/TD]

</tbody>

Thanks for looking

Neo

 

[Ron Coderre]

With your sample data in A1:A19
This regular formula, copied down, returns the ending numbers from each referenced cell

B1: =IFERROR(LOOKUP(10^10,--RIGHT(A1,{1,2,3,4,5,6,7,8,9,10})),"n/a")

Is that something you can work with?

 

[arthurbr]

Try this, entered as an arry formula with Ctrl+Shift+Enter
=IFERROR(MID(A1;MATCH(FALSE;ISERROR(1*MID(A1;ROW(INDIRECT("1:100"));1));0); 100-SUM(1*ISERROR(1*MID(A1;ROW(INDIRECT("1:100"));1))))*1;"")

 

[Neokey]

Tank you for all your help.