Look Who it Was Random video

Excel - Dates from Text to Columns - Episode 732

525 views · Published 12 February 2009 · 2:07 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Using Text to Columns in Excel and preventing dates.

Welcome back to the MrExcel netcast! I'm Bill Jelen and with baseball season in full swing, we have a great baseball-related question today. Jonathan has downloaded some data from the web and it includes the date, opponent, location (indicated by @ for away games), result, and score. However, when he tries to use the "Text to Columns" feature to separate the result and score into two columns, he runs into a problem. The date is being converted to a date format in the second column. Let's take a look at how we can fix this issue.

First, we'll use the "Data" tab and select "Text to Columns". Then, we'll click "Finish" without making any changes. As you can see, all the scores have been converted to dates. For example, 7-3 becomes July third and 6-4 becomes June fourth. This is not what we want, so let's undo that and go back to the original data.

To prevent the scores from being converted to dates, we need to take some extra steps in the "Text to Columns" feature. In step one, we'll select "Delimited" as our data type. In step two, we'll choose "Space" as our delimiter. Now, in step three, we need to pay attention. We'll click on the field that contains the scores and select "Text" as the data format. This tells Excel not to try and convert the scores to numbers or dates. When we click "Finish", you'll see that the scores are now imported as text instead of being converted to dates.

So, the next time you encounter this issue with the "Text to Columns" feature, just remember to select "Text" as the data format in step three. Thanks to Jonathan for sending in this question and thank you for tuning in. Be sure to join us next time for another netcast from MrExcel. Don't forget to like, comment, and subscribe for more Excel tips and tricks. See you soon!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction to MrExcel netcast 
(00:13) Baseball season and related question 
(00:23) Downloading data from the web 
(00:35) Issue with "Data" "Text to Columns" 
(00:58) Solution to converting scores to dates 
(01:08) Extra steps in "Data" "Text to Columns" 
(01:35) Avoiding unwanted conversions 
(01:45) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Avoiding date conversion in Excel Text to Columns
Converting scores to text in Excel Text to Columns
Excel data conversion problem with Text to Columns
Fixing date conversion issue in Excel Text to Columns
How to separate win and score columns in Excel
Importing text as numbers in Excel Text to Columns
MrExcel netcast tutorial on using Text to Columns
Solving Excel Text to Columns date problem
Step-by-step guide for using Text to Columns in Excel
Tips for successful Text to Columns in Excel
Troubleshooting Excel Text to Columns issues
YouTube tutorial on Data Text to Columns in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152167/



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.

More from this channel