Forum Discussion
Twiggyinc
Sep 06, 2023Copper Contributor
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 ...
- Sep 06, 2023
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"
Twiggyinc
Sep 07, 2023Copper Contributor
@Sergei, Thank you very much for your quick and complete response. Implemented and working perfectly. Much appreciated. C
SergeiBaklan
Sep 07, 2023Diamond Contributor
Twiggyinc , you are welcome