# formula to extract the number from each cell

#### Neokey

##### New Member
Hi,

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

Last edited:

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### Ron Coderre

##### MrExcel MVP
With your sample data in A1:A19
This regular formula, copied down, returns the ending numbers from each referenced cell
Code:
``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

##### Well-known Member
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

##### New Member
Tank you for all your help.

Last edited: