Excel Parse out a list of numbers from text (several numbers from one cell)

Question

I need to parse out a list of tracking numbers from text in excel. The position in terms of characters will not always be the same. An example:

Location ID 987

Your package is arriving 01/01/2015

Fruit Snacks 706970554628 
<http://www.fedex. com/Tracking?tracknumbers=706970554628> 

Olive Oil 709970554631 
<http://www.fedex. com/Tracking?tracknumbers=709970554631> 

Sign 706970594642
 <http://www.fedex .com/Tracking?tracknumbers=706970594642> 

Thank you for shopping with us!

The chunk of text is located in one cell. I would like the results to either be 3 separate columns or rows looking like this:

706970554628 , 709970554631 , 706970594642

There will not always be the same number of tracking numbers. One cell might have six while another has one.

Thank you for any help!!

Answer 1

I think you'll need some VBA to do this. And it's not going to be super simple stuff. @Gary'sStudent has a great example of grabbing numbers from a big string. If you need something that is more specific to your scenario you'll have to parse the string word by word and have it figure out when it encounters a tracking number in the URL.

Something like the following will do the trick:

Function getTrackingNumber(bigMessage As String, numberPosition As Integer) As String
    Dim intStrPos As Integer
    Dim arrTrackNumbers() As Variant

    'create a variable to hold characters we'll use to identify words
    Dim strWorkSeparators As String
    strWordSeparators = "()=/<>?. " & vbCrLf


    'iterate through each character in the big message
    For intStrPos = 1 To Len(bigMessage)

        'Identify distinct words
        If InStr(1, strWordSeparators, Mid(bigMessage, intStrPos, 1)) > 1 Then 'we found the start of a new word

            'if foundTrackNumber is true, then this must be a tracking number. Add it to the array of tracking numbers
            If foundTrackNumber Then
                'keep track of how many we've found
                trackNumbersFound = trackNumbersFound + 1

                'redim the array in which we are holding the track numbers
                ReDim Preserve arrTrackNumbers(0 To trackNumbersFound - 1)

                'add the track
                arrTrackNumbers(trackNumbersFound - 1) = strword
            End If

            'Check to see if the word that we just grabbed is "tracknumber"
            If strword = "tracknumbers" Then
                foundTrackNumber = True
            Else
                foundTrackNumber = False
            End If

            'set this back to nothing
            strword = ""
        Else
            strword = strword + Mid(bigMessage, intStrPos, 1)
        End If
    Next intStrPos

    'return the requested tracking number if it exists.
    If numberPosition > UBound(arrTrackNumbers) + 1 Then
        getTrackingNumber = ""
    Else
        getTrackingNumber = arrTrackNumbers(numberPosition - 1)
    End If

End Function

This is a UDF, so you can use it in your worksheet as a formula with:

 =getTrackingNumber(A1, 1)

Which will return the first tracking number it encounters in cell A1. Consequently the formula

 =getTrackingNumber(A1, 2)

will return the second tracking number, and so on.

This is not going to be a speedy function though since it's parsing the big string character by character and making decisions as it goes. If you can wrangle Gary's Student's answer into something workable it'll be much faster and less CPU intensive on larger data. However, if you are getting too many results and need to go at this like a surgeon, then this should get you in the ballpark.

Answer 2

If tracking is always a 12 digit number, then select the cell run run this short macro:

Sub parser117()
    Dim s As String, ary, i As Long
    With ActiveCell
        ary = Split(Replace(Replace(.Text, Chr(10), " "), Chr(13), " "), " ")
        i = 1
        For Each a In ary
            If Len(a) = 12 And IsNumeric(a) Then
                .Offset(0, i).Value = a
                i = i + 1
            End If
        Next a
    End With
End Sub