I came across this functionality the other day when I was trying to convert some data I had in a Microsoft Word document into a Microsoft Excel spreadsheet. The problem was that although the data was in columns in the Word document, it wasn’t actually in a table. This means that when I tried to copy a row into Excel, instead of it going into three columns the entire row went into the first column. It was a major problem for me because there was a significant amount of data, and I didn’t have time to try to hand copy it row by row into Excel. Fortunately for me, this is when I found out about the Text to Columns feature in Excel.

Basically, this functionality allows you to take delimited columns of text and seperate them into separate Excel columns. Delimited in this context means that there are spaces, commas, or some other unique character that seperates the columns of data in your Word document. This allows Excel to tell where one column ends and another begins.

Let’s begin with the following data:

In this case, we have three rows of data with spaces seperating the data in each column. When I just copied the rows from Word to Excel, it put each row in one column, as shown in the screenshot above. This won’t do, as I need three columns of data. Looks like it’s time to fire up the Text to Columns function.

First, select the three cells that have our data in it - A1, A2, and A3. Then from the Data menu, select Text to Columns…. The following dialog will appear:

At the top of the dialog, you can see that Excel has tried to make a determination as to what is the best way to seperate the columns. In this case, it has determined that spaces seperate each column so the Fixed Width radio button is checked by default. If we wanted to use a different delimiter, or multiple delimiters, then we would select the Delimited radio button (in that case, another dialog would appear instead of the one shown below that would allow you to specify what delimiter to use). For this example, we’ll use the default selection and click Next which will bring up the dialog below:

In this step, we can manipulate how the columns will be split out using line breaks. These line breaks are shown as the black lines seperating the data in the preview window. They look good for this example, but you could add, move, or remove them if you wanted to. Since our preview looks good, we’ll click Next to continue bringing up the last dialog in the wizard:

For the last step, we need to define how we want the data in each column formatted. You don’t have to specify a specific format, as it defaults to General. However, if for instance we want the second and third columns to be numeric, we could specify it here by using the radio buttons in the Column data format section. Also in this section, you can choose to not import one of the columns by selecting the Do not import column (skip) option. In the preview, “skip” will appear above any column that will not be imported at the end of the wizard. The Advanced button also allows for additional formatting.

The Destination selection box is where we’ll specify where the final result of the wizard will go. Since we don’t want to overwrite the original data, we’ll choose a few cells down at A7. Clicking Finish will produce the following results:

There we have it, all of the example data is now cleanly seperated into three separate columns. There are some extra spaces in the data, which could be removed in the wizard by moving the line breaks as was discussed earlier.

Overall, this is a pretty easy tool to use and it also makes life much easier when you run into situations like the example above.