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.

Walton Vale 1977

UK HQ

Mr K Sewell of M&R Motor Co

Richard Tipper

East Ham 1338

Widnes 1293

Amersham 1878

Peterborough 1484

Letchworth 2361

Norwich Thorpe 1365

Hexham A470

Smethwick 1540

Manchester 1235

Stockport 1582

Swadlincote 1946

Abbie Cardie

Paul Dowty

Basildon 1156

Gloucester 2485

<tbody>
</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.