Extract Numbers

[kevinboo]

Hi,

I have searched the board, and found numerous posts regarding my question, but unfortunately no solution.

I have a sheet with 1009 lines on containing (in Col A), a mixture of text and numbers, please see the example below.

<TABLE style="WIDTH: 446pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=594 border=0 x:str><COLGROUP><COL style="WIDTH: 446pt; mso-width-source: userset; mso-width-alt: 21723" width=594><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl1207 id=td_post_2525414 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 446pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=594 height=20>1 BROWN HOUSE YO29 5SD 18597266 18259467 Depot London</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1 WEST DRIVE ROAD HU29 4LX 20041981 20364591 Depot Leeds</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10 STARRET CLOSE LE9 6RH 20158932 20115698 Depot Leicster</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20><TABLE style="WIDTH: 467pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=623 border=0 x:str><COLGROUP><COL style="WIDTH: 467pt; mso-width-source: userset; mso-width-alt: 22784" width=623><TBODY><TR style="HEIGHT: 15pt" height=20><TD id=td_post_2525414 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 467pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=623 height=20>Brome House, Peglington, Minchand, Yorkshire, DL55 7RT 20677891 18725312 Depot Leeds</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
As you can see there is no pattern to data, and some lines contain commas (and other things), some don't. What I need to do is to extract the 8 digit number which is placed immediatly after the post code (so in line 1 the number 18597266) and place it in a cell on it's own for each line.

Any ideas?

 

[jasonb75]

JASON - I've done it before (some time ago) but I've forgotten how to input a formula using the Ctrl+Shift+Enter keys, can you explain how to do this please?

Exactly how it says, enter the formula to the cell (without the {}) then press shift ctrl enter.

 

[kevinboo]

Sandeep / Jason / Steve,

Thanks guys all sorted now and all working, again many thanks for your help and prompt replies, hope you all have a great week-end, take care.

Kevin