Forum Discussion

Twiggyinc's avatar
Twiggyinc
Copper Contributor
Sep 06, 2023
Solved

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's avatar
    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"
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 07, 2023

peiyezhu 

Unfortunately terms of use for such tools are not clear. How secure they are, which information collect, etc.  RegEx could be used with recently introduced Python for Excel. Here are also some questions, but at least rules of the game are defined. With that main question do we trust Microsoft sharing our data or not.

In any case, if alternative exists I'd avoid external engines.

Resources