extract 12-character alphanumeric - varying positions of the serial numbers within the text field

[Anonymous]

Good afternoon!  

I have a general text field that could contain a paragraph of text and within it, will contain 2  or 3 serials that I would like to extract and enter into new columns.

The only constant is that the serial numbers are 12-character alphanumeric.

Please see below 3 rows examples from my dataset. I have highlighted in Bold the Serials that need to be extracted.

dk-3190. New f5x6120v8769. f2z519600446.

DK1150 ERZ103XL8042 NEW ERZ1006E6670 FK1150ERT165

DV9560Y,FR1035ZD2154,FR1007424213

I would really appreciate your help.

Thanks in advance

[Anonymous]

Hi @dufoq3, Thank you so much for helping me so promptly..

Your solution looks like magic! After testing so many things with no succes I am so impressed with your code that helped me achive this. 

Many many thanks!

Best wishes 🙂

[dufoq3]

You're welcome. Enjoy.

[dufoq3 · Accepted solution]

Hi @Anonymous, you can choose if you want a list of serial numbers or merged in one cell separated by comma.

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYu7DsIwDAB/xcpcItuxnWZvuoA6RIhHo2y0Czsgvh7Ujne6q9U9nodACT1MyxtW/RgxvvpoycPKX6VkiCLmXeuqG45EipDLTBhupx6FYcrXXaBls4gwblEuZzLdr0tSw3s3lv+l88CksgFGYWEKrrUf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_SerialNumbers = Table.AddColumn(Source, "Serial Numbers", each 
        [ a = Text.SplitAny([Column1], " ,."),
          b = List.Select(a, (x)=> Text.Length(x) = 12 and List.ContainsAll( {"a".."z"} & {"0".."9"}, Text.ToList(x), Comparer.OrdinalIgnoreCase) )
        ][b], type list),
    Ad_SerialNumbersMerged = Table.AddColumn(Ad_SerialNumbers, "Serials Numbers Merged", each Text.Combine([Serial Numbers], ", "), type text)
in
    Ad_SerialNumbersMerged