Power Query - Split by Space - Except text

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
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.

Code:
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi there,
This should work, assuming your data sits in “Table1”
Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top