# Power Query - Split by Space - Except text

#### Sayth

##### Board Regular
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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"})),
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

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.

1,207,094
Messages
6,076,549
Members
446,212
Latest member
KJAYPAL200

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

### Which adblocker are you using?

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

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