Text To Columns in Excel

by Gregory on July 13, 2010

Text to Columns - Before Data

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.

Text To Columns Wizard Step 1 of 3

In the second step I wanted to combine the month and day fields into one column.

Text To Columns Wizard Step 2a

Double-clicking the break line between the Month and Day fields allowed me to combined these fields.

Text To Columns Wizard Step 2b

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.

Text To Columns Wizard Step 3a

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.

Text To Columns Wizard Step 3b

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.

Update: Warning

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.

Related Posts Plugin for WordPress, Blogger...
Rony January 2, 2012 at 7:33 pm

Hi! excelent post, I made one similar but not more completed than yours:
http://runakay.blogspot.com/2012/01/separating-one-cell-text-in-columns.html

Gregory January 2, 2012 at 10:41 pm

Your post is dealing with a simple case of taking all the info from one text column and spreading it out with Excel’s Text to Columns functionality. A good primer for what I’m doing here in my post, parsing out specific data in the text beforehand to get the date/time elements to work.

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: