Excel Text to Columns

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


There are many situations where you need to split up text into separate columns.  Names, phone numbers, dates, addresses and lot’s more can be managed with Excel’s Text to Columns,

Text to Columns allows you take data from one column, and split it out into multiple columns. This can be used to split full names or addresses up into separate columns, pull part of the data from one column out into a separate column, or myriad other uses.

First we’d like to warn about a big, enormous trap in Text to Columns …

Warning – overwrites existing data

For reasons passing understanding, normal computing practice and plain commonsense, Text to Columns will overwrite the source column!   Here’s a very simple column of text that needs splitting into town names and state in different columns.

img 5d9ac4d19737a - Excel Text to Columns

Run it through Text to Columns with a comma separator and this is the result.

excel text to columns microsoft excel 31402 - Excel Text to Columns

See that Column D has been replaced with the first column of the split text.  Ouch.  That’s bad if something went wrong, which it did in this example because one of the locations didn’t have a separating comma.

Happily, Undo will reverse Text to Columns but it’s normal computing practice to keep the source data intact … just in case.

At step 3 of the Text to Columns wizard, always change the Destination to another column, probably the next to the right. In this case, change $D$1 to $E$1.

excel text to columns microsoft office 31403 - Excel Text to Columns

Delimited Text – Splitting up Names

We’ll start with a simple one, splitting full names up into given name and surname columns.

First, select the data you want to split, and go to Data | Data Tools | Text to Columns.

excel text to columns microsoft office 31404 - Excel Text to Columns

The first page of the Convert Text to Columns Wizard is where you specify what kind of data you are splitting. We have words with spaces between them, so we choose Delimited.

Fixed width would be used if you wanted to split out a certain number of characters from each field. We will look at an example of this later.

excel text to columns microsoft office 31405 - Excel Text to Columns

The second page of the wizard is also about the data that you are splitting, and is where you give more information about the format of that data.

excel text to columns microsoft office 31406 - Excel Text to Columns

Our data has a space between the words we want to separate, so we tick Space. If you think there may be more than one space anywhere, it is a good idea to also tick “Treat consecutive delimiters as one”. Otherwise the extra space will be treated as another value and you will get a blank cell, with the surname thrown across into a third column.

The preview box at the bottom shows how the data will be split, so scan down that to check that everything looks all right. When you’re happy with it, click Next.

The third page of the wizard is where you specify what happens to the data after the conversion.

excel text to columns microsoft office 31407 - Excel Text to Columns

As we’ve said already, the important part here is the Destination field. If you don’t change this, the converted data will overwrite the existing data, which may not be what you want. I want to keep the original column and place the new format data next to it, so I have chosen cell B1.

Show your work

Note that if you need to look more closely and see the fields that the wizard is covering up to work out where to put the data, you can click the arrow button at the right of the Destination field to hide most of the wizard. Click the arrow again to show the full wizard again.

excel text to columns microsoft office 31408 - Excel Text to Columns

When we’re happy with our settings, click Finish, and our names will be split into two columns.

excel text to columns microsoft office 31409 - Excel Text to Columns

This is a good example of splitting two words into separate cells.  However, in practice, Flash Fill might be a better choice.  Flash Fill can be ‘trained’ to cope with common name variations like middle names or initials, surname prefixes like ‘Van’ etc.

Fixed Width – Separating text

For this example we have a list of numbers that are actually phone numbers entered without any formatting.

excel text to columns microsoft office 31410 - Excel Text to Columns

We want to separate out the three-digit area code from the phone numbers, so we again select the data and click on the Text to Columns button.

This time, however, as it is always the same number of characters that we want separated, we select the Fixed Width option.

excel text to columns microsoft office 31411 - Excel Text to Columns

This time when we get to the second page, we can see that there is a ruler above the data preview. The numbers on the ruler represent the number of characters in the data.

excel text to columns microsoft office 31412 - Excel Text to Columns

As we want to separate out the first three digits of the numbers, we click after the third digit, and a line will appear to mark the break point. If we put the line in the wrong place, we just click and drag it to move it. If we put in an extra line that we don’t need, just double-click on it to remove it.

Then we click Next to go on to the third page again.

excel text to columns microsoft office 31413 - Excel Text to Columns

Set the Destination cell of where we want our separated data to start, and click Finish. We now have the numbers split up into area codes and phone numbers.

excel text to columns microsoft office 31414 - Excel Text to Columns

Fixed Width – separating area code

A more common situation is separating a phone area code in brackets from the main number.  For example  (303)555-1234  into columns for area code and number.

That’s possible with the Fixed Width option and putting the brackets into their own columns with breaks like this.

excel text to columns microsoft office 31415 - Excel Text to Columns

Two ways to handle unwanted columns

There are two ways to handle the unwanted columns – the official way and the cautious choice.

The official choice is ‘Do not import (skip)’ which is great if you’re absolutely sure the Text to Columns split works 100% of the time.  In other words that the data is consistent.

excel text to columns microsoft office 31416 - Excel Text to Columns

The more cautious choice is to let the unwanted columns go through into new columns.  Then you can check if the split text is correctly done. You’ll end up with columns like this:

excel text to columns microsoft office 31417 - Excel Text to Columns

Delete the columns with brackets (B and D) and you’re good to go.

Advanced Settings

These are just a couple of simple examples that don’t come near to exploring the full range of things that can be done with this feature. We also haven’t looked at some of the more advanced settings in the wizard that would be used for more complex operations, but we’ll just touch on them briefly here.

Text Qualifier

If you are using delimited data the second page of the wizard has a field called Text Qualifier, which has three options: none, a single quote mark, or a double quote mark.

excel text to columns microsoft office 31418 - Excel Text to Columns

This is used if your data contains instances of the delimiter character within the text itself, that needs to be treated as regular text and not as a place to split the data. For example, with our name data, if some of the people had middle names, and we didn’t want a separate middle name column, but just wanted these to go in the Given Name column with the first name, we would need to enclose both of the given names in the qualifier character, which tells Excel to ignore any spaces within that data.

So if we select the double quote mark (“) as our text qualifier, our input data would need to look like this:

excel text to columns microsoft office 31419 - Excel Text to Columns

Which would cause both names within the quote marks to go into the given name column.

excel text to columns microsoft office 31420 - Excel Text to Columns

Column Data Format

This section on the third page of the wizard allows you to set the format for each of the columns that will be created. Click on each of the columns in the preview and select a format for it. If one of the columns is to hold dates, also select the date format.

Skip or do not import column

If part of the data is not required, you can select “Do not import column” and no column will be created for that data.

Below is an example where the original data include people’s names and dates of birth, as wall as a code, all in the one cell. We want to split up the names and dates of birth, and discard the codes.

excel text to columns microsoft office 31421 - Excel Text to Columns

Recognize numeric data

The Advanced button on the third page of the wizard contains settings for how to recognize numeric values in the source data – whether decimals and thousands are indicated by a comma or a dot, and whether a dash after a number should be regarded as a negative number.

excel text to columns microsoft office 31422 - Excel Text to Columns

The decimal and thousand settings will default to the settings of the Regional Settings of your computer, and generally would not need to be changed unless you are importing data from another country that uses a different number format.

 


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.