How do I extract the 17-alphanumeric characters in a column, delimiter

[stephchnva]

Hello guys,

I need help. How do I extract the 17-alphanumeric characters in the "name" column.

I've tried different ways of extracting by delimiter but it's just not working.

Name

24/10/2024 / 1HGCM82633A123456 / Alex Johnson

VF1XYZ9Z9L0456789 / 06/11/2024 / Jamie Smith

20 Dec 2023 / David

Thank you!

[Omid_Motamedise]

Hi @stephchnva 

Copy the below code and paste it into the advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzTQNzIwMlHQVzD0cHf2tTAyMzZ2NDQyNjE1A4o55qRWKHjlZ+QV5+cpKMXqRCuFuRlGREZZRln6GACVmFtYAlUZmOkbGsKM8UrMzUxVCM7NLMmA6DAyUHBJTVYAShsDpV0SyzJTgBKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Select(Text.Split([Name]," / "),(x)=>Text.Length(x)=17){0})
in
    #"Added Custom"

[ThxAlot]

For simplicity and accuracy, incorporate regular express by python script,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzLCsIwEEV/ZchaSGYS02ZZLCpFV4JoQxehBhJoUvGFn2+guL3nnGstI8VRcBKkgAPud5tjTVrKBkmqtS5bM/kvdHPIzzkDG1aWnbd4ufamNwdRlKo2xRKaI/5vOpeih1OKr7AUJKD1IxQsC27dJ94WgJWb7sHld/KPOEJObBh+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Added pq" = Table.AddColumn(Source, "pq", each List.Select(Text.Split([Name], " / "), each Text.Length(_) = 17 and not Text.Contains(_, " ")){0}?),
    #"Run Python script" = Python.Execute("df['py re'] = df['Name'].str.findall(r'\w{17}').apply(lambda match: match[0] if len(match)>0 else '')",[df=#"Added pq"]),
    Result = #"Run Python script"{[Name="df"]}[Value]
in
    Result

[PhilipTreacy · Accepted solution]

@stephchnva 

Download example PBIX file with the code below

= try List.Select(Text.Split([Name], " / "), each Text.Length(_) = 17){0} otherwise null 

Regards

Phil

[ThxAlot]

= Table.AddColumn(dataset, "pq", each List.Select(Text.Split([Name], " / "), each Text.Length(_) = 17 and not Text.Contains(_, " ")){0}?)