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.

Splitting the contents of cells is the opposite of merging cells or using the CONCATENATE function to combine the contents of cells.

  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.

    Data Tools group on the Data tab

  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 among columns by using functions, or click Help button image in the Convert to Text Columns Wizard.

Top of Page

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!