SOLVED

Split Text on change from number to letter

Copper Contributor

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

 

5 Replies
best response confirmed by Twiggyinc (Copper Contributor)
Solution

@Twiggyinc 

If like this

image.png

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"
Split Text on change from number to letter by regular expression online tool

https://b23.tv/KGRYsep

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

@Sergei, Thank you very much for your quick and complete response. Implemented and working perfectly. Much appreciated. C

@Twiggyinc , you are welcome

1 best response

Accepted Solutions
best response confirmed by Twiggyinc (Copper Contributor)
Solution

@Twiggyinc 

If like this

image.png

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"

View solution in original post