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 (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.), 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.

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

Data Tools group on the Data tab

  1. 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 Top of Page

 
 
Applies to:
Excel 2013