Formula to segregate numbers into 2 columns

[Paul van Kampen]

Hi,

I have a list that looks as follows;

<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17>SEAL# 6361462 CPPU # 232980</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17>SEAL# 6361451 CDAU# 35435</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17>CN Rail-CNRU 236647 (Calgary)</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17>cppu 231314 seal# 6361496</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17>cp tr. # 230577 for calgary # 6</TD></TR></TBODY></TABLE>

There is no format to how the cell can be entered, and the information comes from many different sources. I'd like to have a column that will only show numbers so that I can use other functionality to sort through the data. It would be particularly affective if it looked like this;

Column A
6361462
6361451
236647
231314
230577

Column B
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17>232980</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17>35435</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17></TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17>6361496</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=205 border=0 x:str><COLGROUP><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1797169 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 154pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=205 height=17>6</TD></TR></TBODY></TABLE>

 

[pgc01]

Hi Paul
Welcome to the board

Use a udf, for ex. in B1:

=getnumber($A1,COLUMNS($B1:B1))

Copy down and accross

with the udf code:

Function GetNumber(r As Range, lInd As Long) As String
Dim rexMatches As Object
 
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+"
    .Global = True
    Set rexMatches = .Execute(r)
    If rexMatches.Count >= lInd Then GetNumber = rexMatches(lInd - 1).Value
End With
End Function

Test:


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">SEAL# 6361462 CPPU # 232980 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">6361462</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">232980</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">SEAL# 6361451 CDAU# 35435 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">6361451</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">35435</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">CN Rail-CNRU 236647 (Calgary) </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">236647</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">cppu 231314 seal# 6361496 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">231314</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">6361496</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">cp tr. # 230577 for calgary # 6 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">230577</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">cp rail # 1234 for calgary # 8 pt #21</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1234</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">8</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">21</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=6 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[texasalynn]

Welcome to MrExcel board....

you might be able to use this

 

[Paul van Kampen]

Yes! It works great, thank you very much!

~Paul

 

[Paul van Kampen]

My preference is the udf code, because I want to have this task automated as much as possible. I appreciate the feedback!

 

[texasalynn]

My post related to a UDF, but the UDF by pgc01 seems much cleaner for your needs

Thanks for the feedback

 

[pgc01]

I'm glad it helped. Cheers!