The Text to Columns function in Excel is a useful feature if you need to split the contents of one cell into separate columns. It’s also simple to use, making it an accessible tool for Excel beginners and experts alike.

When Do I Use Text to Columns?

There are many cases in which Text to Columns can be useful, but let’s start with a straightforward example.

Say you have a list of full names you want to separate into two columns: one for first names, one for last names.

One option is to re-type their first names in one column and their last names in another. Another is to copy and paste parts of the names into the two separate columns.

While both of these options work, Text to Columns is an easier, faster solution.

This tool can be applied to several scenarios. You can use Text to Columns when you need to extract a string of characters in a list of IDs, or when you need to pull user IDs in a list of emails, or when you need to separate a list of dates into separate columns for month, date and year.

In this guide, you’ll learn how to use the Text to Columns function.

How to Use Text to Columns

1. Select the column that has the list of data you need to separate.

2. On the Data tab, click Text to Columns.

3. You’ll see two options on the screen that pops up: Delimited and Fixed Width. Delimited allows you to separate text based on characters (such as spaces or commas). Fixed Width allows you to separate a fixed number of characters into different columns.

Let’s first explore the Delimited option. Choose “Delimited” and click Next.

4. In this example, the names are listed in the following format: Last Name, First Name. The last and first names are separated by a comma and a space.

Check the boxes for Comma and Space as delimiters, which will split the text where there is a comma and a space.  

Keep in mind that depending on how your list is formatted, you may want to use the other delimiters on this screen.

Take, for instance, a list of email addresses. To separate the user IDs in a list of emails, you would choose Other as the delimiter and enter the “@” character in the option field. This will split the text where there is an “@” symbol.

5. Click Finish for the result. The first and last names are now separated into their own columns.

How to Use the Fixed Width Option for Text to Columns?

Recall there were two options for using Text to Columns. Now that we know how to use the Delimited option, let’s review Fixed Width.

The Fixed Width option allows you to separate a fixed number of characters into separate columns.

For this example, let’s try to extract the last four digits in a list of bank account numbers.

The first two steps are the same, so we’ll start from Step 3.

3. Choose the “Fixed Width” option.

4. Select the point at which you want to break the text into separate columns.

In this example, we only want to extract the last four digits. Click the point right before the fourth to last digit, and a separator will appear to indicate where the break will occur.

Keep in mind that you can have multiple breaks if you need to split the text into more than two columns. You can also drag and move the separator if you need to adjust the break, or remove it entirely if you decide a break isn’t needed at that point.

5. Click Finish for the result. The last four digits of the account numbers are now extracted in a separate column.

Let’s Wrap Up

Next time you need to break up text into different columns, consider using the Text to Columns function. It’s easy to use and allows you to work with a large volume of data in an efficient way.