Extract all 10 character codes from within a freeform text field

[JugglerJAF]

I have a data extract from an accounting system in which operators record 10 character serial numbers along with other bits of information in a free format text field.

What I need to do is to extract from that free format text field any text strings which are 10 characters in length.

Example 1:
LaserJet X1234Y Printer ABC1D23456 Z1234Y

Example 2:
GH567T5 3.0GHz 1P SCSI EU Server Product: 123498765 ABC12345DE

Example 3::
AB789 G3 X2.8GHz Server ProductID: 123456987 A1BCDEF23G ABCDEF234G

The required output for example 1 would be ABC1D23456

The required output for example 2 would be ABC12345DE

The required output for example 3 would be A1BCDEF23G as well as ABCDEF234G (either in separate columns or delimited by a character such as underscore in a single column)

Not all cells will contain a 10 digit serial number (which is expected, and either N/A or blank should be returned in these instances), but some cells will contain multiple serial numbers (up to 50 or possibly even more).

As you can see, there is absolutely no consistency in input (the bane of my life!). The serial number could be the first 10 characters, the last 10 characters (unlikely, but possible) or fall somewhere in the middle of the text string (in 95% of cases).

Even the serial numbers themselves are not consistent in their pattern of letters/numbers as they cover many different product types and descriptions. Most are a mixture of letters and numbers, some are all numeric and some are all alpha characters. When there are multiple serial numbers in a single cell they could be separated by commas, semi colons, spaces, forward or backward slashes - anything at all really.

I can't think of any way of extracting all the 10 character codes from within the text field either by formula or macro, but before I go back to my Boss and say that I can't do it (other than manually going through thousands of records each month), I thought I'd run the problem past the good folks of MrExcel to see if they had any suggestions.

Over to you...

 

[GTO]

...When there are multiple serial numbers in a single cell they could be separated by commas, semi colons, spaces, forward or backward slashes - anything at all really...

Off the top, hopefully the "anything at all" is exaggerated. If you can come up with a list of all possible seperators, I would think that you could replace seperators with a space, then analyze and retain any seperated vals w/a len of 10. Regular Expressions comes to mind...

 

[AlphaFrog]

This macro does basically two things. It replaces your specific "separators" with spaces and then looks for any so-called "word" (Alpha, Numeric, or Alpha-Numeric) that has a length of 10 characters. Admittedly, this wont be a 100% perfect solution, but given the criteria you've outlined, it's as close as I could get. It worked well for your three examples.

It loops through the text strings in column A and outputs to columns B and C etc. as needed.

Sub Serial_Numbers()

    Dim rng As Range, i As Long, counter As Long
    Dim arrSeperators As Variant, arrSplit As Variant
    
    Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp))    'Text is in column A
    
    arrSeperators = Array(",", ";", ":", "-", "\", "|")
    
    ' Replace all specified seperators with spaces
    For i = LBound(arrSeperators) To UBound(arrSeperators)
        rng.Replace What:=arrSeperators(i), Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, _
                    MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next i
    
    For Each cell In rng
        arrSplit = Split(cell.Text)
        
        counter = cell.Column + 1
        For i = LBound(arrSplit) To UBound(arrSplit)
            
            If Len(arrSplit(i)) = 10 Then
                Cells(cell.Row, counter).Value = arrSplit(i)
                counter = counter + 1
            End If
            
        Next i
        
    Next cell
    
End Sub

 

[JugglerJAF]

Many thanks for your assistance. That works perfectly.

There is still some manual work to do as the code extracts any 10 digit string so I'm getting pieces of text other than serial numbers ("director's" for instance!), but they're easy to spot in the trimmed data.

 

[AlphaFrog]

Glad it works.

If there are a set of 10 character "words" that occur repeatedly (like director's), you can include them in the arrSeperators list and they will be removed by the macro.

Why is the wise man with great vision in a well?

 

[JugglerJAF]

Glad it works.

If there are a set of 10 character "words" that occur repeatedly (like director's), you can include them in the arrSeperators list and they will be removed by the macro.

Why is the wise man with great vision in a well?

Good idea, I'll add in the comon ones as you've suggested.

As for the wise man, the fool pushed him in (before climbing the mountain!)