Forum Discussion

Twiggyinc's avatar
Twiggyinc
Copper Contributor
Sep 06, 2023

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 and I want to Split by Row to create CONSPLIT. 

By observation when a number changes to a letter then I want is split but I do not know enough of the M Code and the Column by Examples doesn't seem to do it.  I have other data on the row that I want to duplicate for the "new" number of CONSPLIT. 

 

Below is an example what I am trying to do with ~27,000 rows of data. 

 

Thanks in advance

 

CONALL CONALLCONSPLIT
JL6291IL5801 JL6291IL5801JL6291
JL6291.1 JL6291IL5801IL5801
JL2201JL6291 JL6291.1JL6291.1
JL6102JL6291 JL2201JL6291JL2201
IL2661-1IL2669-2 JL2201JL6291JL6291
JL1222 JL6102JL6291JL6102
IL3661-1IL3661-2 JL6102JL6291JL6291
JL5301JL5311 IL2661-1IL2669-2IL2661-1
JL1222JL2201 IL2661-1IL2669-2IL2669-2
JL5311JL5312IL6601 JL1222JL1222
IL3661-1 IL3661-1IL3661-2IL3661-1
JL2201IL2661-1IL2661-2IL2669-2 IL3661-1IL3661-2IL3661-2
  JL5301JL5311JL5301
  JL5301JL5311JL5311
  JL1222JL2201JL1222
  JL1222JL2201JL2201
  JL5311JL5312IL6601JL5311
  JL5311JL5312IL6601JL5312
  JL5311JL5312IL6601IL6601
  IL3661-1IL3661-1
  JL2201IL2661-1IL2661-2IL2669-2JL2201
  JL2201IL2661-1IL2661-2IL2669-2IL2661-1
  JL2201IL2661-1IL2661-2IL2669-2IL2661-2
  JL2201IL2661-1IL2661-2IL2669-2IL2669-2

 

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
      MVP
      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.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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"

Resources