Distribute the contents of a cell into adjacent columns

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

  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.

Excel Ribbon Image

  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 names by using the Convert Text to Columns Wizard, or click Help Button image 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.

Top of Page Top of Page

 
 
Applies to:
Excel 2007