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]

For example

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

 

[JEC]

This could be enough as well, but I don't know how the rest of your data looks like

Function jec(cell As String) As String
 Dim it
 For Each it In Split(Replace(cell, ".", " ."))
    If IsNumeric(it) And Len(it) >= 7 And Len(it) <= 9 Then jec = jec & IIf(jec = "", "", ",") & it
 Next
End Function

 

[JamesCanale]

For the interest of anyone with a similar problem and excel365:

MrExcelPlayground8.xlsx
	A	B
1	adkjasdf 123456789. adfa456 1234567 fdgja;gf 987654321 asdfja;df	123456789, 1234567, 987654321
Sheet19

Cell Formulas
Range	Formula
B1	B1	=LET(s,IF(ISNUMBER(VALUE(MID(A1,SEQUENCE(LEN(A1)),1))),MID(A1,SEQUENCE(LEN(A1)),1)," "),a,TRIM(TEXTJOIN("",TRUE,s)),b,FILTERXML("<x><y>"&SUBSTITUTE(a," ","</y><y>")&"</y></x>","//y"),c,TEXTJOIN(", ",TRUE,IF(SIGN((b<1000000)+(b>999999999)),"",b)),c)

 

[JEC]

Or with a direct approach within the xml path.

Logboek 2022.xlsm
	A	B
1	adkjasdf 123456789. adfa456 1234567 fdgja;gf 987654321 asdfja;df	123456789, 1234567, 987654321
2	123456789. adfa456 999999 fdgja;gf 987654321 asdfja;df	123456789, 987654321
3	adkjasdf 2364569. adfa456 1234567 fdgja;gf 9854763	2364569, 1234567, 9854763
Blad1

Cell Formulas
Range	Formula
B1:B3	B1	=TEXTJOIN(", ",,FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(A1,"."," .")," ","</y><y>")&"</y></x>","//y[(number() and string-length() >=7 and string-length() <= 9)]"))

 

[soccer4ward]

@JEC
i am forever in your debt!

thank you!

 

[JEC]

You're welcome!

 

[Fluff]

@soccer4ward Please do not put your reply inside quotes, as it looks as though you have simply quoted someone else & not said anything.

 

[smozgur]

@JEC
i am forever in your debt!

thank you!

The marked solution has been changed as confirmed.

@soccer4ward - in your future questions, please mark the post that answered your question instead of your feedback post. This way it will help future readers. No action is necessary for this one since I already switched the marked solution.

 

[soccer4ward]

Good Morning JAC,

Is there a way to code this so that it ignores numerical strings longer than 9 digits? Upon testing, we found that sometimes data can contain numbers in it that are longer than 9 digits. In those cases, the code ends up grabbing just the first 9 digits of it. We do not want that. If there are more than 9 consecutive numbers in a portion of a string, we want it ignored.

thanks so much!