How to get only numbers from junk text in Excel

A

arjun5381

New Member
Joined
Nov 12, 2015
Messages
8
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
 
Last edited:
Sort by date Sort by votes
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

*ABCD
ABC999989898998_XYZ0998(TUV)44848333423_RKT2323_6669866666
EXT_LL_DLC_1004005125-UW_AP_624-Georgepeta**
1008023651EXT_LL-EXOTH|BAT(Wipro)_1008023651_T10,Ecity|Equant(Airtel Pop), WF|2 Mbps|E1-G.703|CBRN 2*
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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:236px;"><col style="width:168px;"><col style="width:169.6px;"><col style="width:169.6px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]999989898998[/TD]
[TD="align: right"]44848333423[/TD]
[TD="align: right"]6669866666[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1004005125[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1008023651[/TD]
[TD="align: right"]1008023651[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1014144871[/TD]
[TD="align: right"]1014144871[/TD]
[TD="align: right"]1014144871[/TD]

</tbody>


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
 
Upvote 0
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
Click to expand...
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
 
Upvote 0
Rick Rothstein said:
Can you explain the rule that lets you keep 999989898998 from ABC999989898998 but doesn't let you keep 0998 from XYZ0998?
Click to expand...
Probably...
arjun5381 said:
I want to get numbers only from a junk text field in Excel, where number length is >9 digits,
Click to expand...


@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
ABCDE
1
2ABC999989898998_XYZ0998(TUV)44848333423_RKT2323_666986666699999898989984484833342366698666669
3EXT_LL_DLC_1004005125-UW_AP_624-Georgepeta1004005125
41008023651EXT_LL-EXOTH|BAT(Wipro)_1008023651_T10,Ecity|Equant(Airtel Pop), WF|2 Mbps|E1-G.703|CBRN 210080236511008023651
5Ext_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_11179000101414487110141448711014144871
6
Sheet1
 
Last edited:
Upvote 0
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...
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
Click to expand...
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top