Going back to Podcast 453, I talked about taking data from the Mega-Millions website and parsing this data in Excel. Dave wrote in to say that the text to columns process was not working out well. In Episode 566, I show how using Text to Columns sometimes requires a few extra steps.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question that's sent in by Dave.
Dave was watching an old podcast number 453.
We're talking about taking data from the Mega Millions, site and getting it in Excel and in that podcast. I showed how to do a Pivot table.
But, Dave had a simpler question, he said wait a second.
How did you get the data from this website?
Where they have the date, semi colon, colon, the numbers with commas, semicolon, Mega Ball, colon, 10 and get it into the columns like you did.
So, I basically copied that data.
I just pasted it into Excel and I mentioned in the podcast.
I just kind of threw off a comment, that said, I use Data, Text to Columns.
Well, it wasn't quite that simple, I had to go through and use Data, Text to Columns, a few different times.
The one thing I noticed was that we have a semicolon that seems to separate the date and the numbers.
So, the first thing I did was, I use Data, Text to Columns and said that my data was Delimited.
In the second step, I said it was Delimited by a semicolon and click Finish.
So, that basically got me three columns, the first column is a pretty nice column with just the date.
The next column, is a column with all of the numbers, and then finally the Money Ball column.
Now, the first thing I would do is.
I would separate this Money Ball column.
Use Data, Text to Columns, this time say that my data is delimited and separated by a colon.
Now, we don't have a colon, so, I'm gonna have to put colon in there.
In the next step, I'm going to get rid of the text that says Money Ball.
So, I'll click do not import and then click Finish.
So, now I have my Money Ball column, that works fairly well I'll label that.
The next problem, then as I looked through Column B.
I see that some of these start with a colon and others don't as I scroll down, there's some that have colon's and others don't.
So, I'm going to select that entire column and use Edit, Replace and say that I want to replace every occurrence of a colon with nothing, Replace All, Click OK.
And now we're ready to do a Text to Columns, to separate out the numbers.
I know there's about five numbers for each one.
So, I'm gonna insert for extra columns, select the data set, Data, Text to Columns.
Again, my data is Delimited. This time it's delimited by just the comma.
I'll uncheck everything else, click Finish and there we have it.
All the original numbers and the Money Ball, in the right most column.
And actually in the podcast, I had gone through and had Date in column A, number in Column B there was no magic for that, that was simple brute force copying.
Basically, copying columns A and B somewhere, then copying columns A and C, A and D, A and E, A and F and finally A and G into a new data set.
So, that would be easy to pivot.
So, sometimes with Data, Text to Columns, you have to look at the original data set, see if there's some way that you can break it down into a few different problems in this case, I had to do three text to columns as well as an edit replace in order to get the data clean.
Hey, thanks for stopping by.
See you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question that's sent in by Dave.
Dave was watching an old podcast number 453.
We're talking about taking data from the Mega Millions, site and getting it in Excel and in that podcast. I showed how to do a Pivot table.
But, Dave had a simpler question, he said wait a second.
How did you get the data from this website?
Where they have the date, semi colon, colon, the numbers with commas, semicolon, Mega Ball, colon, 10 and get it into the columns like you did.
So, I basically copied that data.
I just pasted it into Excel and I mentioned in the podcast.
I just kind of threw off a comment, that said, I use Data, Text to Columns.
Well, it wasn't quite that simple, I had to go through and use Data, Text to Columns, a few different times.
The one thing I noticed was that we have a semicolon that seems to separate the date and the numbers.
So, the first thing I did was, I use Data, Text to Columns and said that my data was Delimited.
In the second step, I said it was Delimited by a semicolon and click Finish.
So, that basically got me three columns, the first column is a pretty nice column with just the date.
The next column, is a column with all of the numbers, and then finally the Money Ball column.
Now, the first thing I would do is.
I would separate this Money Ball column.
Use Data, Text to Columns, this time say that my data is delimited and separated by a colon.
Now, we don't have a colon, so, I'm gonna have to put colon in there.
In the next step, I'm going to get rid of the text that says Money Ball.
So, I'll click do not import and then click Finish.
So, now I have my Money Ball column, that works fairly well I'll label that.
The next problem, then as I looked through Column B.
I see that some of these start with a colon and others don't as I scroll down, there's some that have colon's and others don't.
So, I'm going to select that entire column and use Edit, Replace and say that I want to replace every occurrence of a colon with nothing, Replace All, Click OK.
And now we're ready to do a Text to Columns, to separate out the numbers.
I know there's about five numbers for each one.
So, I'm gonna insert for extra columns, select the data set, Data, Text to Columns.
Again, my data is Delimited. This time it's delimited by just the comma.
I'll uncheck everything else, click Finish and there we have it.
All the original numbers and the Money Ball, in the right most column.
And actually in the podcast, I had gone through and had Date in column A, number in Column B there was no magic for that, that was simple brute force copying.
Basically, copying columns A and B somewhere, then copying columns A and C, A and D, A and E, A and F and finally A and G into a new data set.
So, that would be easy to pivot.
So, sometimes with Data, Text to Columns, you have to look at the original data set, see if there's some way that you can break it down into a few different problems in this case, I had to do three text to columns as well as an edit replace in order to get the data clean.
Hey, thanks for stopping by.
See you next time for another netcast from MrExcel.
