How to get only numbers from junk text in Excel

[arjun5381]

I want to get numbers only from a junk text field in Excel, where number length is >9 digits, if in same text have more than one number combination in a same cell so this will split in different cells

Example :
Cell (A2) : ABC999989898998_XYZ0998(TUV)44848333423_RKT2323_6669866666
Result :
Cell (B2) : 999989898998 Cell (C2) : 44848333423 Cell (D2) : 6669866666

Sample Data as below :

(1) EXT_LL_DLC_1004005125-UW_AP_624-Georgepeta

(2) 1008023651EXT_LL-EXOTH|BAT(Wipro)_1008023651_T10,Ecity|Equant(Airtel Pop), WF|2 Mbps|E1-G.703|CBRN 2

(3) Ext_LL-DLC-TCL-1014144871-2 Mbps-Jaipur-Jaipur-RJ-G.703-Default-16-Mar-2010_111790Ext_LL-DLC-TCL-1014144871-2 Mbps-Jaipur-Jaipur-RJ-G.703-Default-16-Mar-2010_111790AExt_LL-DLC-TCL-1014144871-2 Mbps-Jaipur-Jaipur-RJ-G.703-Default-16-Mar-2010_11179000

 

[Peter_SSs]

(I'm interested in Peter_SSs's solution)...

I'd also make use of the dictionary object, but I'd stick to the UDF approach. The advantages of a UDF include ..

- If the data (column A in this case) changes the result will automatically update.
- If you want to change the length of the number being searched for (eg from >9 to >7 digits) there is no need to edit the code as it's easy to change that in the formula.
- If needed, you could just extract, say, the second such number from the string without needing to extract the lot.

Anyway, this would be my adaptation to the latest request.

Function ExtractNum(s As String, MinLength As Long, Optional Occur As Long = 1) As Variant
  Dim d As Object, Nums As Object
  Dim e As Variant
  
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d{" & MinLength & ",}"
    Set Nums = .Execute(s)
  End With
  Set d = CreateObject("Scripting.Dictionary")
  d.comparemode = 1
  For Each e In Nums
    d(Val(e)) = 1
  Next e
  ExtractNum = d.keys()(Occur - 1)
End Function

Again, B2 below is copied across and down.

Excel Workbook
	A	B	C	D	E
1
2	ABC999989898998_XYZ0998(TUV)44848333423_RKT2323_66698666669	999989898998	44848333423	66698666669
3	EXT_LL_DLC_1004005125-UW_AP_624-Georgepeta	1004005125
4	1008023651EXT_LL-EXOTH|BAT(Wipro)_1008023651_T10,Ecity|Equant(Airtel Pop), WF|2 Mbps|E1-G.703|CBRN 2	1008023651
5	Ext_LL-DLC-TCL-1014144871-2 Mbps-Jaipur-Jaipur-RJ-G.703-Default-16-Mar-2010_111790Ext_LL-DLC-TCL-1014144871-2 Mbps-Jaipur-Jaipur-RJ-G.703-Default-16-Mar-2010_111790AExt_LL-DLC-TCL-1014144871-2 Mbps-Jaipur-Jaipur-RJ-G.703-Default-16-Mar-2010_11179000	1014144871
6
Sheet1