Split Text on change from number to letter

I have a number of simple codes back to back and I want to split them out based only on when it goes from number to letter. Ideally I want to edit in Power Query. CONALL being the original data ...

BI & Data Analysis

excel

Formulas and Functions

power bi

SergeiBaklan

Sep 06, 2023

Twiggyinc

If like this

the script could be

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),

    #"Added Custom" = Table.AddColumn(
        #"Promoted Headers",
        "CONSPLIT",
        each Splitter.SplitTextByCharacterTransition({"0".."9"},
            (c) => not List.Contains({"0".."9"} & {"-", "."}, c))([CONALL])  ),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Added Custom",
        {"CONSPLIT"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "CONSPLIT")
in
    #"Expanded Custom"

CONSPLIT.xlsx18 KB

Forum Discussion

Split Text on change from number to letter

Resources