Todd has lottery data in a single column and needs to break the data into various columns. Episode 1024 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a two minute video every workday to learn one of 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a two minute video every workday to learn one of 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. Basically we start out with massive amount of Data.
How we're gonna analyze as well plus fire up a pivot table. You can solve this problem.
Welcome back to the MrExcel Netcast.
I'm Bill Jelen. Today's question sending by Todd.
Todd has data from the Minnesota lottery and it all comes into a single cell.
They don't put the commas between the various values.
He says how can I split those out to various columns ?
and we're going to use a couple of tricks here.
First of all to select the whole Column. control shift down arrow and then on the data tab, we're gonna use "Text to Columns".
Now in excel 2003 you go to the data menu and choose "Text to Columns".
and we're going to say that in this data actually we have a choice.
We either say that it's fixed width and that would work because they were very careful to put the leading zeros here and the dates or we could say that it's delimited based on a space either one would work how to choose fixed width.
You see if they put all of the lines in here, if any of those were wrong we could drag it to a new place or follow the instructions up here to double click to the lead or click to add a new one.
We will click next.
Now you want to make sure that this first field the date field is declared as a date in month day year format.
The others you can just leave as general.
Click finish and bam.
Here we are. Everything is broken out into various columns and we're good to go.
you can now start to do some sort of a pivot table or something along those lines.
Hey there you have it. Thanks for stopping by.
I'll see you next time for another netcast from MrExcel
How we're gonna analyze as well plus fire up a pivot table. You can solve this problem.
Welcome back to the MrExcel Netcast.
I'm Bill Jelen. Today's question sending by Todd.
Todd has data from the Minnesota lottery and it all comes into a single cell.
They don't put the commas between the various values.
He says how can I split those out to various columns ?
and we're going to use a couple of tricks here.
First of all to select the whole Column. control shift down arrow and then on the data tab, we're gonna use "Text to Columns".
Now in excel 2003 you go to the data menu and choose "Text to Columns".
and we're going to say that in this data actually we have a choice.
We either say that it's fixed width and that would work because they were very careful to put the leading zeros here and the dates or we could say that it's delimited based on a space either one would work how to choose fixed width.
You see if they put all of the lines in here, if any of those were wrong we could drag it to a new place or follow the instructions up here to double click to the lead or click to add a new one.
We will click next.
Now you want to make sure that this first field the date field is declared as a date in month day year format.
The others you can just leave as general.
Click finish and bam.
Here we are. Everything is broken out into various columns and we're good to go.
you can now start to do some sort of a pivot table or something along those lines.
Hey there you have it. Thanks for stopping by.
I'll see you next time for another netcast from MrExcel
