Jonathan notices a problem when he uses the Text to Columns wizard. Baseball scores such as 4-3 are converted to dates. In Episode 732, we'll take a look at how to keep those scores from being converted.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Well it's baseball season here in the United States.
So we have a good baseball related question today. Jonathan sends this question and he's downloading this data from the web somewhere.
It has the date the opponent the @ of course means that it was an away game or no @ sign is a home game, and then the result they won and the score and Jonathan says.
When I do "Data" "Text to Columns" to break this win and the score in two separate columns, I'm having a problem. I'm getting a date as the second field.
So we'll use "Data" "Text to Columns" and just click finish and sure enough you see that all the scores are converted to dates. 7-3 becomes july third and 6-4 becomes June fourth.
So let me undo that and we'll go back to the original data.
What we have to do is we have to take some extra steps here in "Data" "Text to Columns" and pay attention in step 3. So in step one we say our data is delimited.
In step two is delimited by a space and then in step 3, we're going to click on the field that has the score and say that we want to import that as text.
Text says. Hey, we're not going to try and convert this to a number.
We're not going to get rid of the leading and trailing spaces and we're not going to try and convert it to a date and when we click finish, sure enough, you'll see that the scores come in as text instead of being converted to a date.
Anytime the "Text to Column" starts doing that to you converting values to either dates or something you dont want it to do, just choose text and you'll be good to go.
Thanks to Jonathan for sending in that question and thanks to you for stopping by.
Will see you next time for another netcast from MrExcel.
Well it's baseball season here in the United States.
So we have a good baseball related question today. Jonathan sends this question and he's downloading this data from the web somewhere.
It has the date the opponent the @ of course means that it was an away game or no @ sign is a home game, and then the result they won and the score and Jonathan says.
When I do "Data" "Text to Columns" to break this win and the score in two separate columns, I'm having a problem. I'm getting a date as the second field.
So we'll use "Data" "Text to Columns" and just click finish and sure enough you see that all the scores are converted to dates. 7-3 becomes july third and 6-4 becomes June fourth.
So let me undo that and we'll go back to the original data.
What we have to do is we have to take some extra steps here in "Data" "Text to Columns" and pay attention in step 3. So in step one we say our data is delimited.
In step two is delimited by a space and then in step 3, we're going to click on the field that has the score and say that we want to import that as text.
Text says. Hey, we're not going to try and convert this to a number.
We're not going to get rid of the leading and trailing spaces and we're not going to try and convert it to a date and when we click finish, sure enough, you'll see that the scores come in as text instead of being converted to a date.
Anytime the "Text to Column" starts doing that to you converting values to either dates or something you dont want it to do, just choose text and you'll be good to go.
Thanks to Jonathan for sending in that question and thanks to you for stopping by.
Will see you next time for another netcast from MrExcel.
