Split by dynamic positionof(any) (and so keeping the delimiter)

[ooptennoort]

I want to use "E" as a delimiter but need to keep it...
Using Text.PositionOf([Enumbers],"E",1) I know the position of every Last "E" and want to split by that position. Surely it can be done but the advanced M code...
I have tried variations on:

Splitter.SplitTextByPositions({0, (Text.PositionOf([Enumbers],"E",1)}), {"Enumber 1", "Enumber 2"})
Splitter.SplitTextByPositions({(Text.PositionOfAny([Enumbers],"E",1))}), {"Enumber 1", "Enumber 2"})

even played around with:

Splitter.SplitTextByPositions({0, _(Text.PositionOf([Enumbers],"E",1)}), {"Enumber 1", "Enumber 2"})
Splitter.SplitTextByPositions({each (Text.PositionOfAny([Enumbers],"E",1)}), {"Enumber 1", "Enumber 2"})

Anyone?

Enumbers =Source	Enumber 1 =Result	Enumber 2 =Result
E10861	E10861
E108512	E108512
E3622768E3622769	E3622768	E3622769
E36250E362511999	E36250	E362511999
E1292E1294	E1292	E1294
E11758	E11758
E1290E1291	E1290	E1291
E967	E967

 

[alansidman]

How about this? Would you ever have more than two E's that needs to be split?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([#"Enumbers =Source"] <> null)),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Filtered Rows", "Enumbers =Source", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Enumbers =Source.1", "Enumbers =Source.2", "Enumbers =Source.3"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Enumbers =Source.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Enumbers =Source.2.1", "Enumbers =Source.2.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition1",{"Enumbers =Source.1", "Enumbers =Source.2.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Enumbers =Source.2.2", "Enumbers =Source.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.1")
in
    #"Merged Columns1"

 

[ooptennoort]

How about this? Would you ever have more than two E's that needs to be split?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([#"Enumbers =Source"] <> null)),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Filtered Rows", "Enumbers =Source", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Enumbers =Source.1", "Enumbers =Source.2", "Enumbers =Source.3"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Enumbers =Source.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Enumbers =Source.2.1", "Enumbers =Source.2.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition1",{"Enumbers =Source.1", "Enumbers =Source.2.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Enumbers =Source.2.2", "Enumbers =Source.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.1")
in
    #"Merged Columns1"

Oh of course, Bycharactertransition, also possible! Thank you. But, indeed, there might be a few more E's... do you have an other method in mind than simply extending above method for a few more columns (merging blanks if they are empty), perhaps dynamically?
Still... I am still REALLY curious how to write Splitter.SplitTextByPositions in combination with Text.PositionOf to make it dynamic (so I won't press solved yet, hope you don't mind... )

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = List.Buffer(List.Transform(Source[Enumbers], each List.Transform(List.RemoveItems(Text.Split(_,"E"), {""}), (x)=> "E" & x))),
    MaxCt = List.Max(List.Transform(lst, each List.Count(_))),
    cols = List.Accumulate({1..MaxCt}, {}, (s,c)=> s & {"Enumber." & Text.From(c)}),
    lst1 = List.Transform(lst, each _ & List.Repeat({null}, MaxCt - List.Count(_))),
    Result = Table.FromRows(lst1, cols)
in
    Result

 

[ooptennoort]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = List.Buffer(List.Transform(Source[Enumbers], each List.Transform(List.RemoveItems(Text.Split(_,"E"), {""}), (x)=> "E" & x))),
    MaxCt = List.Max(List.Transform(lst, each List.Count(_))),
    cols = List.Accumulate({1..MaxCt}, {}, (s,c)=> s & {"Enumber." & Text.From(c)}),
    lst1 = List.Transform(lst, each _ & List.Repeat({null}, MaxCt - List.Count(_))),
    Result = Table.FromRows(lst1, cols)
in
    Result

I have a lot to learn, that much is clear

 

[ooptennoort]

Saw a semi-solution by ExcellsFun on youtube:

So the simplest option by far is splitting Digit to Non-Digit. This also solves the "limit of only 2 columns problem". BUT... however... it does -still- not quite answer/solve the question/challenge... so I'll leave it unchecked just a little longer.