Distribute the contents of a cell into adjacent columns

You can split the contents of one or more cells in a column, and then distribute those contents as individual parts across other cells in adjacent columns. For example, if your worksheet contains a column of full names, you can split that column into separate first name and last name columns.

1. Select the cell, range, or entire column that contains the text values that you want to split.

   Note: A range that you want to split can include any number of rows, but it can include no more than one column. You also should keep enough blank columns to the right of the selected column to prevent existing data in adjacent columns from being overwritten by the data that will be distributed. If necessary, you can insert blank columns.

2. On the Data tab, in the Data Tools group, click Text to Columns.

   

3. Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

Note: For help with completing all the steps of the wizard, see the topic, Split text into different columns with the Convert Text to Columns Wizard, or click Help in the Convert to Text Columns Wizard.

Tip: For an alternative method of distributing text across columns, see the article, Split text among columns by using functions.

Tip: You can combine cells with the CONCAT function or the CONCATENATE function.

Top of Page

See Also

Overview of formulas in Excel

How to avoid broken formulas

Find and correct errors in formulas

Excel keyboard shortcuts and function keys

TEXT functions (reference)

Excel functions (alphabetical)

Excel functions (by category)