Power query. Returning a substring from another column.

[Anonymous]

Hi guys. I've trying to find a way to separate a part of a text, but I cant find. I have a random sentence that it has a word I want. This word allways have the pattern: letter-letter-number-letter, Examples: 13A7, 25E9, 63B8...

This are an example of text from where I want to extract:

Text column: "The product 8361529 with batch code 13A7 have the issue number 24865"

Return column: "13A7"

Please imagine this in power query.

Thank you guys and sorry for my bad english.

[Jakinta · Accepted solution]

You can paste this to new blank query and adjust to your query.

2nd step looks for all words of length 4, containing 3 digits with letter on 3rd position.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslIVSgoyk8pTS5RsDA2MzQ1slQwBQKF8sySDIWkxJLkDIXk/JRUBUNjR3OFjMSyVIUSoJbM4uLSVIW80tyk1CIFIxMLM1OlWB3spqEbZGLpSh2DTE3dzahikKGhjylVDDI2C7CkThgZhZpQxSADg0gL/AbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Return", each List.Select( Text.Split ( [Text], " "), each Text.Length(_)=4 and Text.Length( Text.Select(_,{"0".."9"}))=3 and List.Contains({"A".."Z"},Text.ToList(_){2}) ) ),
    #"Expanded Return" = Table.ExpandListColumn(#"Added Custom", "Return")
in
    #"Expanded Return"

Result: