Extracting a sequence in Power Query

let
    chars = List.Transform({"a".."z", "A".."Z"}, (x) => {x, "A"}),
    nums = List.Transform({"0".."9"}, (x) => {x, "#"}),
    replacements_list = List.Buffer(List.Combine({chars, nums})),
    pattern = "-####-##A####-",
    find_pattern = (txt) =>
        [lst = Text.ToList(txt), 
        rpl = List.ReplaceMatchingItems(lst, replacements_list),
        t = Text.Combine(rpl), 
        find_position = Text.PositionOf(t, pattern, Occurrence.First), 
        res = if find_position <> -1 then Text.Range(txt, find_position + 1, 11) else null][res],
    // edit Source to get your original data
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    new_col = Table.AddColumn(Source, "pattern", (x) => find_pattern(x[column_name]), type text)
in
    new_col

Awesome AlienSx ...

Hi JohnHarker ,

Give something like this a go, its a bit verbose but does the trick.

let
    Source = Table.FromRows({
        {"519-1-16800 Work in progress-WHQ projects-Branch (Travaux en cours - Immobilisations)-2234-10S1460-Congo (Kinshasa) Lubumbashi Branch"},
        {"519-1-12910 Prepaid expenses-Service contracts-2294-11S4992-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"519-1-21510 Accrued liabilities payable-Service contracts-2294-11S4993-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"}
    }, type table [Sample = text ]),
    ExtractPattern = Table.AddColumn(Source, "Custom", each  
        Text.Combine( 
            List.Select( 
                List.TransformMany( 
                    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"-"})([Sample]), 
                    each Splitter.SplitTextByCharacterTransition({"-"}, (c) => not List.Contains({"0".."9"}, c))(_),
                    (a, b) => Text.TrimEnd( Text.TrimStart(b, "-"), "-")
                ), (x)=> List.AllTrue(
                    {
                        Text.Length(x) =12, 
                        Text.PositionOf(x, "-") =4, 
                        Text.PositionOf(x, Text.Select(x, {"A".."Z"})) =7
                    }
                )
            ), ", "
        )              
    )
in
    ExtractPattern

with this result

I hope this is helpful

let
    Source = Table.FromRows({
        {"519-1-16800 Work in progress-WHQ projects-Branch (Travaux en cours - Immobilisations)-2234-10S1460-Congo (Kinshasa) Lubumbashi Branch"},
        {"519-1-12910 Prepaid expenses-Service contracts-2294-11S4992-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"519-1-21510 Accrued liabilities payable-Service contracts-2294-11S4993-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"}
    }, type table [Sample = text ]),
    fx = (str, pat) => Web.Page("<script>document.write('"& str &"'.match(/" & pat & "/g))</script>")[Data]{0}[Children]{0}{[Name="BODY"]}[Children][Text]{0}, 
    ExtractPattern = Table.AddColumn(Source, "Custom", each fx([Sample], "\d{4}-\d{2}[A-Z]\d{4}"))
in
    ExtractPattern

NewStep=Table.AddColumn(YourTable,"Extract",each List.RemoveNulls(List.Transform({0..Text.Length([LongTextColumn])-12},(x)=>let a=Text.Range([LongTextColumn],x,11),b=Text.SplitAny(a,{"-","A".."Z"}) in if List.Count(b)<>3 then null else  if List.AllTrue(List.Transform({0..2},(y)=>Text.Remove(b{y},{"0".."9"})="" and Text.Length(b{y})={4,2,4}{y}) then a else null){0}?)

pls try this

let
f=(x)=> Text.AfterDelimiter( Text.Split(x,"-Congo"){0},"-",{1,RelativePosition.FromEnd}),
    Source = Table.FromRows({
        {"519-1-16800 Work in progress-WHQ projects-Branch (Travaux en cours - Immobilisations)-2234-10S1460-Congo (Kinshasa) Lubumbashi Branch"},
        {"519-1-12910 Prepaid expenses-Service contracts-2294-11S4992-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"519-1-21510 Accrued liabilities payable-Service contracts-2294-11S4993-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"}
    }, type table [Sample = text ]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each f([Sample]))
in
    #"Added Custom"

and try this

let
f=(w)=> try  Splitter.SplitTextByRanges(
    {{
         [
             a = List.Transform( {0..9},(x)=>  "-"& Text.From(x)),
             b = List.Transform(a,(x)=>Text.PositionOf(w,x,2)),
             c = List.Sort( List.Combine(b),1){1}+1][c],12}}
             )(w){0} otherwise "",


        Source = Table.FromRows({
        {"519-1-16800 Work in progress-WHQ projects-Branch (Travaux en cours - Immobilisations)-2234-10S1460-Congo (Kinshasa) Lubumbashi Branch"},
        {"519-1-12910 Prepaid expenses-Service contracts-2294-11S4992-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"519-1-21510 Accrued liabilities payable-Service contracts-2294-11S4993-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"ddddddddd"}
    }, type table [Sample = text ]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each f([Sample]))
in
    #"Added Custom"

I hope you’re doing well. I wanted to check in and see if the issue you mentioned has been resolved. If it has, could you please mark the response that helped you as the solution? This will make it easier for other users to find the answer quickly.

Thank you for your cooperation!

Best regards,

Stephen Tao