Power Query - Split by Space - Except text

[Sayth]

Is there a way to split by space as a delimiter but only if the space falls between a number?

So you got some poorly formed excel that has all the data in one column an example I created this. Because if people have different or hyphenated names then that will throw it off so if we can get it to skip the word spaces altogether then it should always hold its columns.

class nuname        score1  score2  score3  score4 
      1 jon smith      43.69   86.20   15.08   41.40
       2 bill van smith   70.56   86.87    6.90   88.77
       3 tanya jones    50.75   42.05   31.16   95.18
       4 ron le bron    36.18   29.95   47.45    8.87
       5 jill jake      93.83   70.50   91.10   52.75
       6 beth mott      60.68   98.70    3.07   28.23
       7 abdul khan     92.57   73.48   84.97   32.88

 

[ImkeF]

Hi there,
This should work, assuming your data sits in “Table1”

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RenameTable = Table.RenameColumns(Source,{{"class nuname        score1  score2  score3  score4 ", "Table"}}),
    SeparateText = Table.AddColumn(RenameTable, "Name", each Text.Trim(Text.Remove([Table],{"0".."9","."}))),
    SeparateNumbers = Table.AddColumn(SeparateText, "Numbers", each Text.Remove([Table],{"A".."z"})),
    AddIndex = Table.AddIndexColumn(SeparateNumbers, "Index", 0, 1),
    CreateListFromNumbers = Table.AddColumn(AddIndex, "List", each Text.Split([Numbers], " ")),
    RemoveBlanks = Table.AddColumn(CreateListFromNumbers, "Custom.2", each Table.AddIndexColumn(Table.FromList(List.RemoveMatchingItems([List], {"", null})),"Inde",0,1)),
    Expand = Table.ExpandTableColumn(RemoveBlanks, "Custom.2", {"Column1", "Inde"}, {"Column1", "Inde"}),
    RemCol = Table.RemoveColumns(Expand,{"List"}),
    ChangeType = Table.TransformColumnTypes(RemCol,{{"Column1", type number}}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(ChangeType, {{"Inde", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(ChangeType, {{"Inde", type text}}, "de-DE")[Inde]), "Inde", "Column1", List.Sum),
    Sort = Table.Sort(Pivot,{{"Index", Order.Ascending}}),
    RemCol2 = Table.RemoveColumns(Sort,{"Table", "Numbers", "Index"}),
    Reorder = Table.ReorderColumns(RemCol2,{"0", "Name", "1", "2", "3", "4"})
in
    Reorder

Here’s some explanation on the technique behind it:
https://social.technet.microsoft.co...umn-at-first-alpha-character?forum=powerquery

 

[Sayth]

That is an interesting solution. Maybe this is something I should do in Python before bringing into excel then it will only be splitting each line into an array type checking concatenate strings and iterating back into a final output.