formula to extract the number from each cell

Neokey

New Member
Joined
Oct 14, 2005
Messages
8
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
 
Last edited:

Some videos you may like

Excel Facts

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

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,315
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
Joined
Dec 8, 2006
Messages
1,934
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;"")
 

Watch MrExcel Video

Forum statistics

Threads
1,090,459
Messages
5,414,647
Members
403,540
Latest member
mmorejon1215

This Week's Hot Topics

Top