Extracting all numbers from a string

[soccer4ward]

Hello,

I am attempting to extract data from a string. I have employees copying tabular data out of another software. The software got updated, so now copied data only gets pasted as a string in one cell. I was hoping someone is able to assist with a VBA code or a formula to extract any numbers from that string. Numbers are 7 to 9 digits long and can be anywhere in the string (since copied data varies from employee to employee depending on how many columns they view in another software). For an example, if i have the following string:

Original string:
adkjasdf 123456789. adfa456 1234567 fdgja;gf 987654321 asdfja;df

Result:
123456789, 1234567, 987654321

As you can see I'm only interested in consecutive 7 to 9 digit numbers. Anything less or more should be ignored.

any help is greatly appreciated!

thank you,

 

[JEC]

You could try the second macro I posted

 

[soccer4ward]

I did try that as well. It works, but then it ignores needed numbers when text is right after them.
i.e. 123456789abc is not being caught because it's not a number, but number and text I'm guessing?

My logical thought (and i don't know if that's possible) is to create a section of the code to "ignore" specific instances that i can add as i come across them.
I'd like to add an "exceptions" section that i can edit on my own so i don't bug you too much. i,e:

>extract all 7 to 9 digit numbers
>unless they are in the following format:
>##########.###.#####
>###-###-#########
>start with a specific digit., etc.,

thanks so much!

 

[JEC]

Ok, try this small change

Function jec(cell As String) As String
 Dim it
 With CreateObject("VBScript.RegExp")
   .Global = True
   .Pattern = "\b\d{7,9}\b"
   If .test(cell) Then
     For Each it In .Execute(cell)
       jec = jec & IIf(jec = "", "", ", ") & it
     Next
   End If
 End With
End Function