How to get only numbers from junk text in Excel

arjun5381 · Nov 13, 2015

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

tlowry · Nov 13, 2015

Welcome to MrExcel!

Here is one attempt, give it a try and post the results...

If this is a large amount of data, it may be worth looking into a more efficient way of doing this.

This code is looking for data in the active worksheet starting in column A

Sheet1

*	A	B	C	D
1	ABC999989898998_XYZ0998(TUV)44848333423_RKT2323_6669866666	999989898998	44848333423	6669866666
2	EXT_LL_DLC_1004005125-UW_AP_624-Georgepeta	1004005125	*	*
3	1008023651EXT_LL-EXOTH\|BAT(Wipro)_1008023651_T10,Ecity\|Equant(Airtel Pop), WF\|2 Mbps\|E1-G.703\|CBRN 2	1008023651	1008023651	*
4	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	1014144871	1014144871

Excel tables to the web >> Excel Jeanie HTML 4

Code:

Sub Main001()
    Dim lr: lr = Cells(Rows.Count, 1).End(xlUp).Row
    Dim cCell
    For Each cCell In Range("A1:A" & lr).Cells
        ExtractNumbers cCell
    Next
End Sub

Sub ExtractNumbers(cCell)
    Dim i, shold, hld
    With cCell
        For i = 1 To Len(CStr(.Value))
            Select Case Mid(.Value, i, 1) Like "[0-9]"
                Case True
                    shold = shold & Mid(.Value, i, 1)
                Case Else
                    shold = shold + " "
            End Select
        Next i
        shold = Split(shold, " ")
        i = 1
        For Each hld In shold
            If Len(hld) > 9 Then
                .Offset(0, i) = CStr(hld)
                i = i + 1
            End If
        Next hld
    End With
End Sub

Rick Rothstein · Nov 13, 2015

arjun5381 said:
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

Can you explain the rule that lets you keep 999989898998 from ABC999989898998 but doesn't let you keep 0998 from XYZ0998? They both start with three letters followed by several digits up to a non-digit, so I am confused at why one of them is kept and the other is not. If that is a mistake and you really want every standalone number to be listed, then see if this code works...

Code:

Sub ExtractNumbersOnly()
  Dim X As Long, Cell As Range, CellText As String, Nums() As String
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    CellText = Cell.Value
    For X = 1 To Len(CellText)
      If Mid(CellText, X, 1) Like "[!0-9]" Then Mid(CellText, X) = " "
    Next
    Nums = Split(Application.Trim(CellText))
    Cell.Offset(, 1).Resize(, UBound(Nums) + 1) = Nums
  Next
End Sub

Peter_SSs · Nov 13, 2015

Rick Rothstein said:
Can you explain the rule that lets you keep 999989898998 from ABC999989898998 but doesn't let you keep 0998 from XYZ0998?

Probably...

arjun5381 said:
I want to get numbers only from a junk text field in Excel, where number length is >9 digits,

@arjun5381
Welcome to the MrExcel board!

My suggestion involves a user-defined function.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy across and down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Rich (BB code):

Function ExtractNum(s As String, MinLength As Long, Optional Occur As Long = 1) As Variant
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d{" & MinLength & ",}"
    ExtractNum = Val(.Execute(s)(Occur - 1))
  End With
End Function

In the function below you specify the string to be assessed, the minimum number of digits to look for (10 in your case) and the particular occurrence to return.
The formula 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

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

Rick Rothstein · Nov 13, 2015

Rick Rothstein said:
Can you explain the rule that lets you keep 999989898998 from ABC999989898998 but doesn't let you keep 0998 from XYZ0998? They both start with three letters followed by several digits up to a non-digit, so I am confused at why one of them is kept and the other is not.

Peter_SSs said:

Probably...

I want to get numbers only from a junk text field in Excel, where number length is >9 digits,

Click to expand...

Click to expand...

Yes, of course. Thanks Peter.

Rick Rothstein said:

If that is a mistake and you really want every standalone number to be listed, then see if this code works...

Code:

Sub ExtractNumbersOnly()
  Dim X As Long, Cell As Range, CellText As String, Nums() As String
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    CellText = Cell.Value
    For X = 1 To Len(CellText)
      If Mid(CellText, X, 1) Like "[!0-9]" Then Mid(CellText, X) = " "
    Next
    Nums = Split(Application.Trim(CellText))
    Cell.Offset(, 1).Resize(, UBound(Nums) + 1) = Nums
  Next
End Sub

Of course, the above code is wrong. Give this a try instead...

Code:

Sub ExtractNumbersOnly()
  Dim X As Long, Cell As Range, CellText As String, Nums() As String
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    CellText = Cell.Value
    For X = 1 To Len(CellText)
      If Mid(CellText, X, 1) Like "[!0-9]" Then Mid(CellText, X) = " "
    Next
    Nums = Split(Application.Trim(CellText))
    For X = 0 To UBound(Nums)
      If Len(Nums(X)) < 10 Then Nums(X) = ""
    Next
    Nums = Split(Application.Trim(Join(Nums)))
    Cell.Offset(, 1).Resize(, UBound(Nums) + 1) = Nums
  Next
End Sub

arjun5381 · Nov 13, 2015

Thanks to all of you guys, now my problem is resolved.

Peter_SSs · Nov 13, 2015

arjun5381 said:
Thanks to all of you guys, now my problem is resolved.

Great, thanks for letting us know.

arjun5381 · Nov 13, 2015

Mr. tlowry, Thank you very much, your code is perfect and now i'm getting correct result.

I required one more help on same question..

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

In this example where (Lenth >9 digits) same numeric values are repeated 4 times, (1008023651)

but in result we get 4 times, here i request you please apply a condition for removing duplidate value.
if Column B,C,D,E...values are duplicates, please give me single cel result.

tlowry · Nov 13, 2015

Here is a suggestion:

Again, if there is lots of data, a more efficient way should be investigated.

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

Code:

Sub Main001()
    Dim lr: lr = Cells(Rows.Count, 1).End(xlUp).Row
    Dim cCell
    For Each cCell In Range("A1:A" & lr).Cells
        ExtractNumbers cCell
    Next
End Sub

Sub ExtractNumbers(cCell)
    Dim i, shold, hld, dict
    Set dict = CreateObject("Scripting.Dictionary")
    With cCell
        For i = 1 To Len(CStr(.Value))
            Select Case Mid(.Value, i, 1) Like "[0-9]"
                Case True
                    shold = shold & Mid(.Value, i, 1)
                Case Else
                    shold = shold + " "
            End Select
        Next i
        shold = Split(shold, " ")
        i = 1
        For Each hld In shold
            If Len(hld) > 9 Then
                If Not dict.exists(hld) Then
                    .Offset(0, i) = CStr(hld)
                    i = i + 1
                    dict.Add hld, dict.Count
                End If
            End If
        Next hld
    End With
End Sub

arjun5381 · Nov 13, 2015

Once Again Thanks a lot Mr. tlowry, this code is perfect.

How to get only numbers from junk text in Excel

arjun5381

New Member

Excel Facts

tlowry

Well-known Member

Rick Rothstein

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

arjun5381

New Member

Peter_SSs

MrExcel MVP, Moderator

arjun5381

New Member

tlowry

Well-known Member

arjun5381

New Member

Forum statistics

Share this page

How to get only numbers from junk text in Excel

New Member

Excel Facts

Well-known Member

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

New Member

MrExcel MVP, Moderator

New Member

Well-known Member

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock