Extract Numerical Data

[HockeyDiablo]

Is there a way to extract numerical data and have it hold its value?

I can write a program to extract:

12 pucks, 456 sticks, 7890 fans to 1234567890
123-456-7890 to 1234567890

but here is the actual outcome I am looking for:

123 345 7890
123 345 7890

I would like to keep the separations true where the number is introduced to a non-number

 

[Scott Huish]

There is no 3 in your first example, but there is in your result. I'm assuming your looking for how to format a number in VBA. But what if there are less than 10 digits in the number, how do you want to handle that?

 

[HockeyDiablo]

The examples should just be numbers in general:

12????? 345???? 5242???? 23???? = 12 345 5242 23
63??? 32? 345??? 234???? 235??? = 63 32 345 234 235
567??????????????????????? 12?? = 567 12
123??? = 123

just looking to have the numbers pulled out

 

[Scott Huish]

Function IsolateNumbers(r As String) As String
Dim m As Object, t, s As String
With CreateObject("vbscript.regexp")
    .Pattern = "\d*"
    .Global = True
    If .test(r) Then Set m = .Execute(r)
    For Each t In m
        s = s & " " & t
    Next
End With
IsolateNumbers = Application.WorksheetFunction.Trim(s)
End Function

 

[HockeyDiablo]

Function IsolateNumbers(r As String) As String
Dim m As Object, t, s As String
With CreateObject("vbscript.regexp")
    .Pattern = "\d*"
    .Global = True
    If .test(r) Then Set m = .Execute(r)
    For Each t In m
        s = s & " " & t
    Next
End With
IsolateNumbers = Application.WorksheetFunction.Trim(s)
End Function

works great until I encounter a monetary value that I needed 19.99 comes back 19 99

Any way to get just a decimal point to work within that?

 

[HockeyDiablo]

also, what about only numbers that have len > 2?

 

[Scott Huish]

Function IsolateNumbers(r As String) As String
Dim m As Object, t, s As String
With CreateObject("vbscript.regexp")
    .Pattern = "\d*\.?\d*"
    .Global = True
    If .test(r) Then Set m = .Execute(r)
    For Each t In m
        s = s & " " & t
    Next
End With
IsolateNumbers = Application.WorksheetFunction.Trim(s)
End Function

Please explain your second question. My code works with numbers with a length greater than 2.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:277px;" /><col style="width:122px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >12????? 345???? 5242???? 23????</td><td >12 345 5242 23</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >63??? 32? 345??? 234???? 235???</td><td >63 32 345 234 235</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >567??????????????????????? 12??</td><td style="text-align:right; ">567 12</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >123???</td><td style="text-align:right; ">123</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >567.44??????????????????????? 12??</td><td >567.44 12</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=isolatenumbers(B1)</td></tr><tr><td >C2</td><td >=isolatenumbers(B2)</td></tr><tr><td >C3</td><td >=isolatenumbers(B3)</td></tr><tr><td >C4</td><td >=isolatenumbers(B4)</td></tr><tr><td >C5</td><td >=isolatenumbers(B5)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>