Extracting a Specific string format from a string of text

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"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY4xCgIxEEWvMqQ2kMxMJoldomAhpLERYgqFLRa0Wdn7q5scwPLNPPivVmVNEiaPqu2quhSDhyCGqeO0zPcnlPX1mBaY37AHS+cYkXjo30MsJOy8BLitxqCAxZML/ip+c6xAAgkkfQFRZ+8Cx05WZybNTrprtpR8HPQrSyMtjecfw6q1Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial #" = _t]),
    pattern = {false, false, true, false},
    chars = List.Buffer({"0".."9", "A".."Z"}),    
    finder = (t as text) =>
        [lst = List.Buffer(List.Select(Text.ToList(t), (x) => List.Contains(chars, x))),
        gen = List.Generate(
            () => [ch = lst, okay = false, serial = {}],
            (x) => not List.IsEmpty(x[ch]) or x[okay], 
            (x) => 
                [okay = List.Transform(List.FirstN(x[ch], 4), (w) => (try Number.From(w))[HasError]) = pattern,
                serial =  if okay then List.FirstN(x[ch], 4) else {},
                ch = List.Skip(x[ch], if okay then 4 else 1)],
            (x) => Text.Combine(x[serial])
        ),
        z = Text.Combine(List.Select(gen, each _ <> ""), ", ")][z],
    out = Table.AddColumn(Source, "Desired Outcome", (x) => finder(x[#"Serial #"]))
in
    out

Hi agc1234 ,

Another approach

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial#", type text}}),
    #"Added Desired Output" = Table.AddColumn(
        #"Changed Type", 
        "Desired Output", 
        each 
            List.Transform(
                Text.Split(Text.Remove([#"Serial#"],{" ", "-"}),"&"), 
                each Text.Start(
                    Text.RemoveRange(_,
                    0,
                    Text.PositionOfAny( 
                        _,
                        {"0".."9"},Occurrence.First
                    )
                ),
                4
            )
        )
    ),
    #"Extracted Values" = Table.TransformColumns(
        #"Added Desired Output", 
        {"Desired Output", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
        )
in
    #"Extracted Values"

Hi agc1234 ,

In Power BI, when we want to process complex and unordered data, adding many steps using power query may not get the desired results. As we know, power query cannot use regular expressions. If we call Python script and use regular expressions to process the data this time, it will become simple. Please refer to

Using regular expressions in power bi desktop - Microsoft Fabric Community

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

Hello agc1234,
same result as Vijay_A_Verma but different approach.

See line four. This one also matches the criteria.

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]),
    UppercasedText = Table.TransformColumns(Source,{{"Serial #", Text.Upper, type text}}),
    Ad_Cleaned = Table.AddColumn(UppercasedText , "Cleaned", each Text.Select([#"Serial #"], List.Buffer({"0".."9", "A".."Z"}))),
    Ad_EachFourCombination = Table.AddColumn(Ad_Cleaned, "Each 4 Combination", each List.Accumulate( 
    {0..Text.Length([Cleaned])-1},
    {},
    (s,c)=> List.RemoveNulls(s & { try Text.Range([Cleaned], c, 4) otherwise null })
), type list),
    Ad_OnlyMatching = Table.AddColumn(Ad_EachFourCombination, "Only Matching", each List.RemoveNulls(List.Transform([Each 4 Combination], each 
      if  (try Value.Is(Number.From(Text.ToList(_){0}), Number.Type) otherwise false) //1st digit check if is number
      and (try Value.Is(Number.From(Text.ToList(_){1}), Number.Type) otherwise false) //2nd digit check if is number
      and (try not Value.Is(Number.From(Text.ToList(_){2}), Number.Type) otherwise true) //3rd digit check if is text
      and (try Value.Is(Number.From(Text.ToList(_){3}), Number.Type) otherwise false) //4th digit check if is number
      then _ else null)), type list),
    Ad_FourDigits = Table.AddColumn(Ad_OnlyMatching, "4 digits", each Text.Combine([Only Matching], ", "), type text),
    RemovedOtherColumns = Table.SelectColumns(Ad_FourDigits,{"Serial #", "4 digits"})
in
    RemovedOtherColumns