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?

 

[SteveO59L]

Does the word "Depot" always follow the number you are looking to extract?

 

[kevinboo]

Unfortunately not Steve, It's the number before that number that I need to extract.

 

[SteveO59L]

Does the word "Depot" always appear in the same place in the sequence then?

 

[kevinboo]

It always appears after the second string of numbers, but this could be anywhere with regards to when it appears. For instance in the first line the Capital D in Depot is around the 41st character (including spaces), whereas in the last line its around the 71st character.

 

[Sandeep Warrier]

How about

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Data</td><td style="font-weight: bold;;">Method 1</td><td style="font-weight: bold;;">Method 2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">1 BROWN HOUSE YO29 5SD 18597266 18259467 Depot London</td><td style="text-align: right;;">18259467</td><td style="text-align: right;;">18597266</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1 WEST DRIVE ROAD HU29 4LX 20041981 20364591 Depot Leeds</td><td style="text-align: right;;">20364591</td><td style="text-align: right;;">20041981</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">10 STARRET CLOSE LE9 6RH 20158932 20115698 Depot Leicster</td><td style="text-align: right;;">20115698</td><td style="text-align: right;;">20158932</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Brome House, Peglington, Minchand, Yorkshire, DL55 7RT 20677891 18725312 Depot Leeds</td><td style="text-align: right;;">18725312</td><td style="text-align: right;;">20677891</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">TRIM(<font color="Purple">RIGHT(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">REPLACE(<font color="Navy">A2,SEARCH(<font color="Blue">"Depot",A2</font>)-1,LEN(<font color="Blue">A2</font>),""</font>)," ",REPT(<font color="Navy">" ",50</font>)</font>),50</font>)</font>)," ",REPT(<font color="Purple">" ",50</font>)</font>),50</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=ExtractNum(<font color="Blue">A2</font>)</td></tr></tbody></table></td></tr></table><br />

Function ExtractNum(sInp As String) As Double
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d{8}"
        .Global = False
        If .test(sInp) Then
            ExtractNum = CDbl(.Execute(sInp)(0))
        End If
    End With
End Function

 

[jasonb75]

As long as there are no 8 digit numbers before the postcode

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 628px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1 BROWN HOUSE YO29 5SD 18597266 18259467 Depot London</TD><TD style="TEXT-ALIGN: right">18597266</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 10pt">1 WEST DRIVE ROAD HU29 4LX 20041981 20364591 Depot Leeds</TD><TD style="TEXT-ALIGN: right">20041981</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 10pt">10 STARRET CLOSE LE9 6RH 20158932 20115698 Depot Leicster</TD><TD style="TEXT-ALIGN: right">20158932</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Brome House, Peglington, Minchand, Yorkshire, DL55 7RT 20677891 18725312 Depot Leeds</TD><TD style="TEXT-ALIGN: right">20677891</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>{=MID(A1,MATCH(1,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),8)),1),0)+1,8)}</TD></TR><TR><TD>B2</TD><TD>{=MID(A2,MATCH(1,IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),8)),1),0)+1,8)}</TD></TR><TR><TD>B3</TD><TD>{=MID(A3,MATCH(1,IF(ISNUMBER(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),8)),1),0)+1,8)}</TD></TR><TR><TD>B4</TD><TD>{=MID(A4,MATCH(1,IF(ISNUMBER(--MID(A4,ROW(INDIRECT("1:"&LEN(A4))),8)),1),0)+1,8)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

 

[SteveO59L]

=MID(A1,(FIND("Depot",A1))-18,8)

Alternatively use Text to Columns, with a " " as the delimiter, and the desired number will be 2 columns to the left of the column with "Depot" in it.

 

[kevinboo]

Hi All,

Many thanks for your help an prompt replies, I've gone with Steves =MID(A1,(FIND("Depot",A1))-18,8) I just changed the 18 to 19 in order to capture the first number of the string.

SANDEEP - I understand the worksheet formulas (that is to say I know where and how to put them in the sheet), but when I put the =ExtractNum(A2) in C2 I get the #NAME? thing? Also the code you show, where and how would I put this?

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?

 

[Sandeep Warrier]

SANDEEP - I understand the worksheet formulas (that is to say I know where and how to put them in the sheet), but when I put the =ExtractNum(A2) in C2 I get the #NAME? thing? Also the code you show, where and how would I put this?

Copy the code. In you Excel window, press Alt+F11. In the new window that opens up, press Alt+I+M. Paste the code in the white area on the right. Press Alt+Q to close the window.

Now in Excel use =ExtractNum(A2)