Dustin has a column with 1 to 16 values separated by ;
There are 8 columns total in the data
He wants the ; separated to rows
The May 2017 update to Power Query adds the ability to split a column to Rows
There are 8 columns total in the data
He wants the ; separated to rows
The May 2017 update to Power Query adds the ability to split a column to Rows
Transcript of the video:
Learn Excel from MrExcel podcast, episode number 2097 Split Delimited Text to New Rows Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
I was just in Sarasota, Florida, Power Excel seminar.
Before the seminar starts, I walk around and talk to the folks over there and Dustin had his Excel 2016 opened, he was ready to show me something that just threw me for a loop pieces he has.
And his case, he had eight columns of data.
And right in the middle, there was just a column that had, anywhere from 1 to 15 values separated by semicolons.
And Dustin says, he wants, anytime that there is a semicolon, he wants to generate a new row.
All the other columns will stay exactly the same but he wants, in this case one, two, three rows to pop off from this, all right.
Oh my god, wow! This is going to be tough.
I'm trying to think of the macro that I can write to do it, you know, may be use Data, Text to Column out here and then have a macro to loop through or something like that.
But for whatever reason, I said, "Wow!" May be, may be, somehow Power Query would be able to do this.
All right and luckily for us, in, for Dustin, he was in Excel 2016.
If you are not in Excel 2016, if you are in 2013 or 2010 using Excel in Windows, you can go, download Power Query.
Now we going to see and want to get data from the file, actually copy the data to New workbook, the 'DustinExample'.
Click Import.
And Power Query is going to ask me which sheet to use.
And I'm going to say that I want to Edit that data.
And, and all looks good. Right here.
This is all the thing we want to split the column, right.
And so it just looks so well.
Let's just see what's in here.
Split 'By Delimiter'.
And the first thing I notice is they already figured out that the, the delimiter is a Semicolon.
All right.
This is never used to happen but it's happening down.
Even if it's a delimiter, it's other or custom, to getting it right.
All right, which is cool.
This dialog box already has this awesome Split ones; the left-most and the right-most delimiter that I wish Excel had.
And now it has gotten smart.
And there is a row split by 'Semicolon' and then, check this out.
Advanced options And I can split into rows.
Split into Rows.
This is just freaking amazing.
All right, so right there.
Invoice Transaction 1001 which was one row is now three rows.
All the information, the order date, the quantity, the name of the product, the ship method, all gets copied.
And it just works in, 1002 had two items; 1003, three items and then 1004, without any semicolons, it adjusts is as it's operational row.
I am like, Holy smokes.
I never knew the Power Query would do that And then, I got to Home and I downloaded the latest additional Office 365 and then clicked the 'What's new' One of the things that was new is this very feature.
I am like, Holy smokes.
That is an amazing, amazing thing.
Lucky for us that, that new updated Power Query came out just, you know, probably weeks before I showed up in Sarasota and Dustin had this problem.
All right.
Dustin has a column with 1 to 16 values separated by semicolon There are 8 columns total in the data.
He wants the semicolon separated into rows.
May 26, 2017 updated the Power Query, adds the ability to split a column to rows.
An amazing, amazing feature.
I want to thank Dusting and everyone in Sarasota for being at my seminar.
I want to thank you fro stopping by.
I'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
I was just in Sarasota, Florida, Power Excel seminar.
Before the seminar starts, I walk around and talk to the folks over there and Dustin had his Excel 2016 opened, he was ready to show me something that just threw me for a loop pieces he has.
And his case, he had eight columns of data.
And right in the middle, there was just a column that had, anywhere from 1 to 15 values separated by semicolons.
And Dustin says, he wants, anytime that there is a semicolon, he wants to generate a new row.
All the other columns will stay exactly the same but he wants, in this case one, two, three rows to pop off from this, all right.
Oh my god, wow! This is going to be tough.
I'm trying to think of the macro that I can write to do it, you know, may be use Data, Text to Column out here and then have a macro to loop through or something like that.
But for whatever reason, I said, "Wow!" May be, may be, somehow Power Query would be able to do this.
All right and luckily for us, in, for Dustin, he was in Excel 2016.
If you are not in Excel 2016, if you are in 2013 or 2010 using Excel in Windows, you can go, download Power Query.
Now we going to see and want to get data from the file, actually copy the data to New workbook, the 'DustinExample'.
Click Import.
And Power Query is going to ask me which sheet to use.
And I'm going to say that I want to Edit that data.
And, and all looks good. Right here.
This is all the thing we want to split the column, right.
And so it just looks so well.
Let's just see what's in here.
Split 'By Delimiter'.
And the first thing I notice is they already figured out that the, the delimiter is a Semicolon.
All right.
This is never used to happen but it's happening down.
Even if it's a delimiter, it's other or custom, to getting it right.
All right, which is cool.
This dialog box already has this awesome Split ones; the left-most and the right-most delimiter that I wish Excel had.
And now it has gotten smart.
And there is a row split by 'Semicolon' and then, check this out.
Advanced options And I can split into rows.
Split into Rows.
This is just freaking amazing.
All right, so right there.
Invoice Transaction 1001 which was one row is now three rows.
All the information, the order date, the quantity, the name of the product, the ship method, all gets copied.
And it just works in, 1002 had two items; 1003, three items and then 1004, without any semicolons, it adjusts is as it's operational row.
I am like, Holy smokes.
I never knew the Power Query would do that And then, I got to Home and I downloaded the latest additional Office 365 and then clicked the 'What's new' One of the things that was new is this very feature.
I am like, Holy smokes.
That is an amazing, amazing thing.
Lucky for us that, that new updated Power Query came out just, you know, probably weeks before I showed up in Sarasota and Dustin had this problem.
All right.
Dustin has a column with 1 to 16 values separated by semicolon There are 8 columns total in the data.
He wants the semicolon separated into rows.
May 26, 2017 updated the Power Query, adds the ability to split a column to rows.
An amazing, amazing feature.
I want to thank Dusting and everyone in Sarasota for being at my seminar.
I want to thank you fro stopping by.
I'll see you next time for another netcast from MrExcel.
