Excel, Power Query: Extract data from cell

Question

I want to extract data in a column within cells. Preferably in Power Query. For example this table:

AB ZX550D X 752
AARZX580D S 859
ABPZ8530D W 555
ABDZ7330D D 555
ABTZS530DH 857
AB Z8380D W 475
AB Z7480D X 475
AB Z9310D D 838

Is it possible to extract the exact data as in the example below?

So, the numbers (red) are always the 3 digits before the D, but there can be multiple D's in the same cell. Greens are always the 5th and 4th character and/or number before the D. But again, there can be multiple D's in the same cell.

Answer 1

I would use the Splitter function to split by the transition from digit to D, then extract the appropriate places:

let
    Source = Excel.CurrentWorkbook(){[Name="Table39"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

//Use splitter function to split on transition from digit to "D"
// then extract the appropriate text ranges
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Parts", each 
      let 
        SplitOnD = Splitter.SplitTextByCharacterTransition({"0".."9"}, {"D"})([Column1]){0},
        len = Text.Length(SplitOnD),
        part1 = Text.Range(SplitOnD,len-5,2),
        part2 = Text.End(SplitOnD,3)
      in 
        Text.Combine({part1,part2},"~"), type text),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Parts", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), {"Parts.1", "Parts.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Parts.1", type text}, {"Parts.2", Int64.Type}})
in
    #"Changed Type1"