Need help on power Query split to column function

spycein

Board Regular
Joined
Mar 8, 2014
Messages
120
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,477
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
959
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
120
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,477
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,477
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
120
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,141,492
Messages
5,706,671
Members
421,462
Latest member
Eame20

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