The URL in the video is Microsoft Forms
Today, a problem from Laura who is trying to audit an ACH file. She needs to break the column into 205 individual columns. When you use Excel Text to Columns, it would be incredibly tedious to click 204 times in step 2 of the Text to Columns Wizard. Today, from the Ignite 2019 Show Floor, a surprisingly easy way to solve this using Power Query's Split Column Repeatedly.
Today, a problem from Laura who is trying to audit an ACH file. She needs to break the column into 205 individual columns. When you use Excel Text to Columns, it would be incredibly tedious to click 204 times in step 2 of the Text to Columns Wizard. Today, from the Ignite 2019 Show Floor, a surprisingly easy way to solve this using Power Query's Split Column Repeatedly.
Transcript of the video:
Hey, this is Bill Jelen from MrExcel.com.
I'm here at Ignite where they gave me this awesome podcast booth for one hour.
They asked if I could record 1 podcast.
I'm like wow, I can do more than that so we're currently on episode #3. Lots of great equipment here.
All very cool but they have a nice little podcast center mug from Ignite.
We're getting ready to record our episode #3 So hang on.
Learn Excel from MrExcel Podcast Episode 2294 Power Query Split ACH file to 205 columns.
Hey welcome back to the MrExcel Netcast. I'm Bill Jelen.
I am at Microsoft Ignite right on the show floor here.
This was recorded at Microsoft Ignite 2019.
I'm Bill Jelen from MrExcel Publishing.
Alright so we're taking a look at some data today.
I was doing a seminar recently and Laura was in my seminar and Laura had an ACH file with 205 columns. Lora is an auditor.
Laura had to audit certain columns and her way to do this was to break this out into 205 columns.
Laura's current method was: Data, Text to Columns, Fixed width and then this.
I can't imagine having to click these little lines 205 times.
I cannot bring myself to do it.
I bail before I get too far. Imagine if you had to do this every single day.
Back in the day we would write a macro to do this, but not anymore.
Today what we're going to do is we're going to take this file right here.
Going to make it into a table, so Ctrl+T click OK.
Or if you just save it as CSV file, that would work as well, and then Data, From a Table or Range.
Power Query loads.
Now power query is a free tool built into Excel 2016, Excel 2019, Office 365 and Power Query has tools that are very similar to what we have in Excel - like Split Column.
But we're going to do it by a number of characters.
Boy, that sounds exactly like what Text to Columns is going to do.
But we're going to break it up by one character.
And then right here, the freaking magic is ***Repeatedly! Repeatedly means just do it as many columns as you see, Click OK.
We're going to ACH dot 1, 2, 3 all the way on the far right hand side. If I drag all the way over out to ACH.205, how awesome is that?
You don't have to click the little thing 204 times to create 205 columns. Home, Close and Load.
And there is our answer we're ready to roll.
That is a case where Power Query is infinitely faster than the other way.
Now in the old days I would have written VBA macro, but you know now with Power Query, a VBA macro just simply is not needed for this anymore, which is beautiful.
All right, hey, catch me next week in Chattanooga, TN.
My last seminar ever. I think they have about 20 seats left.
I'll be doing seminars down in Florida, but after 17 years on the road, I'm done. Buy the book MrExcel LX right here.
This book with the 60 greatest Excel tips of all time.
Check it out - the "i" in the top right hand corner.
If you like what you see here, please SUBSCRIBE and ring that bell.
If you ring the bell it will get you notifications.
Also, thanks to Microsoft. Thanks for listening to our podcast.
We recorded at Microsoft ignite and Microsoft is giving away Microsoft Surface earbuds to our listeners to enter visit AKA Dot MS Slash PodcastSweepstakes. The URL is case insensitive.
Enter before December 15th 2019, go ahead and enter and good luck.
Well, I want to thank you for stopping by here in my podcast booth my new home at Microsoft Ignite - at least my home for the next 44 minutes. Thanks for stopping by.
We will see you next time for another netcast from MrExcel.
I'm here at Ignite where they gave me this awesome podcast booth for one hour.
They asked if I could record 1 podcast.
I'm like wow, I can do more than that so we're currently on episode #3. Lots of great equipment here.
All very cool but they have a nice little podcast center mug from Ignite.
We're getting ready to record our episode #3 So hang on.
Learn Excel from MrExcel Podcast Episode 2294 Power Query Split ACH file to 205 columns.
Hey welcome back to the MrExcel Netcast. I'm Bill Jelen.
I am at Microsoft Ignite right on the show floor here.
This was recorded at Microsoft Ignite 2019.
I'm Bill Jelen from MrExcel Publishing.
Alright so we're taking a look at some data today.
I was doing a seminar recently and Laura was in my seminar and Laura had an ACH file with 205 columns. Lora is an auditor.
Laura had to audit certain columns and her way to do this was to break this out into 205 columns.
Laura's current method was: Data, Text to Columns, Fixed width and then this.
I can't imagine having to click these little lines 205 times.
I cannot bring myself to do it.
I bail before I get too far. Imagine if you had to do this every single day.
Back in the day we would write a macro to do this, but not anymore.
Today what we're going to do is we're going to take this file right here.
Going to make it into a table, so Ctrl+T click OK.
Or if you just save it as CSV file, that would work as well, and then Data, From a Table or Range.
Power Query loads.
Now power query is a free tool built into Excel 2016, Excel 2019, Office 365 and Power Query has tools that are very similar to what we have in Excel - like Split Column.
But we're going to do it by a number of characters.
Boy, that sounds exactly like what Text to Columns is going to do.
But we're going to break it up by one character.
And then right here, the freaking magic is ***Repeatedly! Repeatedly means just do it as many columns as you see, Click OK.
We're going to ACH dot 1, 2, 3 all the way on the far right hand side. If I drag all the way over out to ACH.205, how awesome is that?
You don't have to click the little thing 204 times to create 205 columns. Home, Close and Load.
And there is our answer we're ready to roll.
That is a case where Power Query is infinitely faster than the other way.
Now in the old days I would have written VBA macro, but you know now with Power Query, a VBA macro just simply is not needed for this anymore, which is beautiful.
All right, hey, catch me next week in Chattanooga, TN.
My last seminar ever. I think they have about 20 seats left.
I'll be doing seminars down in Florida, but after 17 years on the road, I'm done. Buy the book MrExcel LX right here.
This book with the 60 greatest Excel tips of all time.
Check it out - the "i" in the top right hand corner.
If you like what you see here, please SUBSCRIBE and ring that bell.
If you ring the bell it will get you notifications.
Also, thanks to Microsoft. Thanks for listening to our podcast.
We recorded at Microsoft ignite and Microsoft is giving away Microsoft Surface earbuds to our listeners to enter visit AKA Dot MS Slash PodcastSweepstakes. The URL is case insensitive.
Enter before December 15th 2019, go ahead and enter and good luck.
Well, I want to thank you for stopping by here in my podcast booth my new home at Microsoft Ignite - at least my home for the next 44 minutes. Thanks for stopping by.
We will see you next time for another netcast from MrExcel.
