Is there a way to copy a string of letters and numbers based on that string containing 3 specific letters, all found within a single cell in excel?

Question

I have an excel spreadsheet that has a column, with each cell filled with pages of data stripped from a database. In each cell, 1 or 2 serial numbers may be defined. There are three known formats of serial numbers: Starting with YV and are 10 digits long. Starting with VNA and are 8 digits long. Starting with SVNA and are 9 digits long. I need to extract every copy of every serial number in that column out to a separate cell. There may be 0, 1, or 2 serial numbers in each cell, and the copied information needs to be left blank, left with a single serial number, or left with 2 serial numbers separated by a comma and a space ("VNA1234A, VNAB4321").

I don't have any code to suggest because all code I've seen in this vein is to extract an entire known word, rather than a whole word or phrase based on a few letters.

Is there some way to do this?

I'd like the serial number VNA3FB00 from the text in the picture to be copied into the F cell to the left. And if there was another serial number, to have that copied as well.

Answer 1

This could be done with a formula and access to TEXTJOIN (available in O365 and Excel 2019). In F2 the formula:

=TEXTJOIN(", ",,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G1,":"," "),","," "),"."," "),";"," ")," ","</s><s>")&"</s></t>","//s[starts-with(., 'YV') and string-length(.) =10 or starts-with(., 'VNA') and string-length(.) =8 or starts-with(., 'SVNA') and string-length(.) =9]"))

You may need to add more substitutions in depending on how your data looks.

---

You could also decide to go with an UDF through VBA. Maybe something along these lines:

Function REGEX(str As String, pat As String) As String

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = pat
    If .Test(str) = True Then
        For Each Mtch In .Execute(str)
            If REGEX = "" Then
                REGEX = Mtch
            Else
                REGEX = REGEX & ", " & Mtch
            End If
        Next
    End If
End With

End Function

You can call this function in F2, through:

=REGEX(G2,"\b(?:YV[^\W_]{8}|S?VNA[^\W_]{5})\b")

Where you may want to sway [^\W_] for [A-Z0-9] if you want to exclude lowercase letters to.

If you are unfamiliar with regular expressions then have a look at the online demo and this introductary post on regex within VBA.

---