I recently downloaded and opened a CSV file from a web browser and one of the data columns was supposed to have date information. There was date-type information, but Excel recognized it as being a text field.
I really needed a valid date range for graphing purposes, so I used the Text to Columns feature to parse the data and convert a date field at the same time. It worked quite well.
In general, here’s how you accomplish this task:
- Select the range, or column that contain the text values
- From the Data tab, Data Tools group, click Text To Columns
- Follow the directions in the Text to Columns Wizard
Using the Text To Columns Wizard
There are three steps in the Text to Column Wizard. In the first step, the wizard correctly surmised that my data had spaces between each field and was of the Fixed Width type.
In the second step I wanted to combine the month and day fields into one column.
Double-clicking the break line between the Month and Day fields allowed me to combined these fields.
In the third step I elected to import all but the second column. This is done by selecting a column header and clicking Do not import column (skip). Notice that all the column headers are labeled with the General format.
The second column, containing the date text, can be formatted as a date by selecting the Date option button and then choosing your format, which I left as MDY.
Click Finish to execute.
If you are not happy with the results you can undo everything by using the keyboard shortcut Ctrl+Z.
View the steps in this video.
Again, you can undo everything the Wizard has done by using the keyboard shortcut Ctrl+Z.
This method works because the Year value supplied by the computer’s System Date is the same as the data in column A. This method will fail to give you the correct Date from any year other than 2010. Check out how to extract a Date using a formula.