How to Use Text to Columns in Excel?

In this lesson, you will learn how to separate the text written in one cell.

What does Text to Columns do?

Text to Columns is the option thanks to which Excel will separate text acoording to your needs and split it between columns. This option is used to separate text, usually copied from another source such as the Internet. Common delimiters include commas, spaces, tabs, slashes, and semi-colons. Excel allows you to select one or a combination of these delimiters based on how your data is structured. I prepared examples, where you’ll see how to use the Text to Columns.

Separate first and last names

Here are names separated by a comma stored in one cell. You want to have them in two different columns. Text to Columns option is most often used to separate first name and last name from each. See how to do it.

Excel Text to Columns Table

How to separate from each other first and last name in Excel? It is very simple. Separating first and last name start with the selection of all cells.

Make sure that there is an empty space on the right hand side. If there is not, this is the point when you need to add additional columns on the right side. In other case you may lost the data which will be overwritten by splitted text.

Excel Text to Columns selected cells table

Then go to the Excel ribbon. On the Data section there is an interesting option Text to Columns.

See also  Understanding OLE Action in Excel

Note: To access this option, you can also use the keyboard shortcut ALT + A + E.

Excel Text to Columns Ribbon button

When you choose Text to Columns Wizard appears in text-to-column. In the first step, select the type of data format that best describes the data source. In this case it is a type Delimited, because the first and last name are separated by a comma.

Excel Text to Columns Convert Text to Columns Wizard

In the second step, select the source text delimiters. In this case it is a comma. Immediately at the bottom of the dialog box you can see how Excel will allocate this text.

Text to Columns Convert Text to Columns Wizard

Skip the third step and leave everything as it is. Click Finish and verify results.

Excel Text to Columns Table name and vorname separated

In this way Excel simply separated from each other names. Now they are in two columns instead of the one.

Fixed width

Let’s assume we have a list of ids imported from external tool.

fixed width

We need to separate first 5 digits to the column B and the rest to the column C.

To separate fixed width text select that as in the picture above. Select Text to Columns button from the ribbon and pick Fixed Width option. Click Next.

convert text to columns fixed width

In the next step drag a line to the desired position. In our case it is five digits from the left hand side. Click Next one more time.

move break line to the desired position

The last step of the text conversion will require making two different decisions:

  1. You need to pick proper data format. I decided to leave it as General.
  2. The second decision is to select the Destination cell. This would be =$B$2 cell because I’d like to put data in columns B and C. You can also pick column A to replace previos data if you don’t need it any more. I decided to keep it to show you the change.
See also  How to Insert Smartart in Excel

set data format

Here is how text has been converted to columns. You may see that first five digits are present in column B and the rest was moved to column C.

text to columns fixed width conversion

From the work experience I can share that fixed width conversion happen quite often. Usually I was using that during sales reports analysis when codes of products has been imported from external tools.

Text separated by spaces

The next example contains car names which are delimited by spaces.

Text to Columns space delimited

We would like to separate a brand of the car from the model.

To separate a brand from its model similarly, select the data and click Text to Columns button from the ribbon above.

In the Convert Text to Columns Wizard pick Delimited option and Space as a delimiter.

space delimiter

Voila! This is how car brands and models has been separated in the spreadsheet.

cars brands and models delimited

Such kind of conversion is not being used so often. This is unusual to separate text by spaces by tools.

Your data should now be split into separate columns based on the delimiter you selected. If you ever need to undo the split, you can use the “Undo” button in the toolbar or press “Ctrl + Z” on your keyboard.