Need help on power Query split to column function

spycein

Board Regular
Joined
Mar 8, 2014
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,
I am looking for a split to column function which splits the value in multiple value on occurrence of starting with numbers i.e numbers between 0-9.
For example, i have a data like following column
Column 1
1 07-Feb-2020 1,275 194H - Commission / Brokerage

I am looking for a function which would return the result as follows

Column 1Column 2Column 3Column 4
107-Feb-20201,275194H - Commission / Brokerage

Hope i have explained my query properly.
Thank you so much in advance.
Best Regards,
Shib
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,344
Office Version
  1. 365
Platform
  1. Windows
If all your data is representative of your sample then

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column 1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column 1.1", "Column 1.2", "Column 1.3", "Column 1.4", "Column 1.5", "Column 1.6", "Column 1.7", "Column 1.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column 1.1", Int64.Type}, {"Column 1.2", type date}, {"Column 1.3", Int64.Type}, {"Column 1.4", type text}, {"Column 1.5", type text}, {"Column 1.6", type text}, {"Column 1.7", type text}, {"Column 1.8", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Column 1.4", "Column 1.5", "Column 1.6", "Column 1.7", "Column 1.8"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
806
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Variation of the same. I'm sure there is a shorter way though.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Text"]}[Content],
    TxtAsDelimitedList = Table.AddColumn(Source, "TextAsList", each { Text.Replace(Text.BeforeDelimiter([Text]," ", Occurrence.Last+1), " ", "|") & "|" & Text.AfterDelimiter([Text], " ", Occurrence.Last+1) } ),
    TxtAsTable = Table.AddColumn(TxtAsDelimitedList, "TextAsTable", each Table.FromList([TextAsList], Splitter.SplitTextByDelimiter( "|", QuoteStyle.None))),
    KeepTxtAsTable = Table.SelectColumns(TxtAsTable , {"TextAsTable"} ),
    ExpandTextAsTable = Table.ExpandTableColumn(KeepTxtAsTable, "TextAsTable", Table.ColumnNames(KeepTxtAsTable[TextAsTable]{0}))
in
    ExpandTextAsTable
 

spycein

Board Regular
Joined
Mar 8, 2014
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Thank you so much for your inputs. Found a much easier way to solve this problem i.e. Split column by position function.
Best Regards,
Shib
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Show us your Mcode, please. It will be helpful for others who may be seeking a similar answer.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
I think it will be similar to this
= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 2, 13, 19}))
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks Sandy. Hoping the OP has a consistent cell accountability, ie. Length and spacing?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
OP's example isn't representative but should be...
I will not write what I think about it (n)
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
I did it with Length, Running Sum and some details
but I'll wait for OP's solution
 

spycein

Board Regular
Joined
Mar 8, 2014
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,
Sorry for the delay in response.
here is m code which i used

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Positions" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByPositions({0, 5, 21, 31, 34}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Column1.1", Int64.Type}, {"Column1.2", type date}, {"Column1.3", Int64.Type}, {"Column1.4", Int64.Type}, {"Column1.5", type text}})
in
#"Changed Type1"

Then i merged the Column 1.4 & 1.5 as column 4

THanks
 

Forum statistics

Threads
1,136,258
Messages
5,674,678
Members
419,520
Latest member
Jennifer4Dillon

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
Top