Forum Discussion

agc1234's avatar
agc1234
New Member
2 years ago

Extracting a Specific string format from a string of text

Good Day Everyone,   I am trying to extract a specific format of: ##a# or (number)(number)(letter)(number)   I need first 4 parts of the serial number. I've attached examples of source data and w...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago

Use below code

Note - In row 4, 87X6 is also a pattern matching your requirement.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY4xCwIxDEb/SuhsoU3StHVrFRyELi5C7aBww8G5nNz/V68dX/Lge7Uqa5IweVTtUNWtGDwFMUwdp3V+LlC292taYf7AESxdY0Tiof8OsZCw8xLgsRmDAhYvLvi7+N2xAgkkkPQFRJ29Cxw7WZ2ZNDvprtlT8nnQvyyNtDSeqrUv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial #" = _t]),
    #"Added Custom2" = Table.AddColumn(Source, "Custom.1", (x)=> 
    let
        p = Text.Select(x[#"Serial #"], {"0".."9", "A".."Z"}),
        c = Text.Length(p)-3,
        r = List.Generate(()=>[i=0, a=Text.Start(p,4)], each [i]<c, each [i=[i]+1, a=Text.Middle(p,i,4)], each [a])
    in
        Text.Combine(List.Select(r, (x)=> List.ContainsAll({"0".."9"}, Text.ToList(Text.Start(x, 2) & Text.End(x, 1))) and Text.Select(x, {"A".."Z"}) = Text.At(x,2)), "; ")    )
in
    #"Added Custom2"