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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
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
 

Sayth

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

Watch MrExcel Video

Forum statistics

Threads
1,102,630
Messages
5,488,000
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top