Microsoft Excel 2003/2004

Splitting Cell Data

When you are working with data in Microsoft Excel, it may sometimes be useful to split cell information such as names (Doe, John) and addresses (Eau Claire, WI) into multiple columns so you can work with each separately. This document offers instructions for splitting cell data.

Commonly, this type of data is separated by a comma or space. These characters, used to separate data within a cell, are called delimiters. Excel allows you to split data based on a number of predefined delimiters or you can specify your own.

Considerations
Splitting the Data

Considerations

Review the cells to standardize the cell data and the delimiters. This will help to ensure a clean separation of data. In other words, you may have this combination: UW-Eau Claire, UW Milwaukee, and University of Wisconsin, Platteville. Using the Find and Replace feature can make this type of "clean up" easier.
Review the cells for separators also used as text characters. For example, Eau Claire WI has a space in its name but also uses the space to separate city and state. This may influence what character you use as a delimiter.
Review cell contents to determine if you need to split your data into three columns (e.g., Jane M. Doe or John Doe Jr.)
Review the cells for delimiters that appear consecutively (e.g., the comma and space in Canton, OH). If your cell data has consecutive delimiters, you will probably want to select the Treat consecutive delimiters as one option. Excel will act as if there were one delimiter instead of two and will not split the data into extra cells. If you use a space as one of your delimiters, Excel automatically selects Treat Consecutive Delimiters As One. If you do not wish to use this option, deselect it.

WARNING: Splitting data writes over both the source data cells and the cells directly to the right of the source data. If there is data in these cells you do not want to lose, you may insert a blank column(s) to the right of the column you are splitting. For more information on adding columns, refer to Working with Rows and Columns.

Splitting the Data

Select the cells containing the data you want to split
NOTE: Excel will split only one column at a time.
From the Data menu, select Text to Columns...
The Convert Text to Columns Wizard - 1 of 3 dialog box appears.
Under Original data type, select Delimited
Click NEXT
Under Delimiters, select the character(s) you want to use to separate the cell data
OPTIONAL: Select or deselect Treat consecutive delimiters as one as appropriate
NOTE: For more information, refer to Considerations.
Deselect any delimiters you do not want to use
The Data preview box shows you what the split data will look like.
NOTE: The Text qualifier pull-down list allows you to select the symbol you wish to use to indicate that the data included within is to be treated as text.
Click NEXT
OPTIONAL: To change the location where the split data will appear on the worksheet, in the Destination text box, type the data destination in the format [$(column letter)$(row number)]
OR
1. Windows: In the Destination text box, click COLLAPSE DIALOG
  Macintosh: In the Destination text box, click COLLAPSE DIALOG
  The mouse pointer will become a cross.
2. Select the cell(s) where you want the split cells to appear
3. Windows: To return to the Convert Text to Columns Wizard, click RESTORE DIALOG
  Macintosh: To return to the Convert Text to Columns Wizard, click EXPAND DIALOG
Click FINISH
If there is any existing data in the cells where the split data will be placed, a dialog box appears asking if you want to replace the content of the destination cells.
To replace the data, click OK
To cancel the cell split and leave the cells as they were, click CANCEL