Microsoft Excel 2007

Separating Cell Data

When working in Excel, you may need to separate cell data such as names (e.g., Doe, John) or addresses (e.g., Eau Claire, WI) into individual columns so you can work with each item separately. This data must be separated by delimiters, such as commas or spaces. Excel allows you to separate cell data either by selecting predefined delimiters or with custom delimiters.

Considerations
Separating the Data

Considerations

There are some things to consider before separating your cells:

To ensure consistent data separation, standardize all cell data and corresponding delimiters. Using Find and Replace can make standardization quicker and easier.
Do not use delimiters that also function as text characters.
EXAMPLE: In Eau Claire WI, the space between Eau Claire and WI could work as a delimiter. But there is also a space operating as a text character between Eau and Claire, which you would not want to separate. One solution could involve separating cities and states with commas and using the comma as your delimiter.
Determine if you should separate your data into three columns (e.g., Jane M. Doe or John Doe Jr.).
Look for consecutive delimiters (e.g., the comma and space in Canton, OH). If your cell data has consecutive delimiters, you should select the Treat consecutive delimiters as one option.
NOTE: 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: Separating data overwrites both the source cells and the cells directly to their right. To avoid deleting adjacent cell data, you can insert one more more blank columns to the right of the column you are separating. For more information on adding columns, refer to Working with Rows and Columns.

Separating the Data

Select the cells containing the data you want to separate
NOTE: Excel can only separate one column at a time.
Select the Data command tab
In the Data Tools group, click TEXT TO COLUMNS
The Convert Text to Columns Wizard dialog box appears.
NOTE: Your data is displayed in the Preview of selected data section.
Under Original data type, select Delimited
Click NEXT
Under Delimiters, select or deselect the appropriate delimiters
The Data preview box shows you how your data will be separated.
NOTE: If you would like to keep some data containing your delimiter from being separated, 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.
OPTIONAL: As appropriate, select or deselect Treat consecutive delimiters as one
HINT: For more information, refer to Considerations.
Click NEXT
OPTIONAL: To change the location where the separated data will appear on the worksheet, in the Destination text box, type the data destination in the format of: [$(column letter)$(row number)]
OR
1. In the Destination text box, click COLLAPSE DIALOG
2. Select the cell(s) where you want the separated data to appear
3. To return to the Convert Text to Columns Wizard, click RESTORE DIALOG
Click FINISH
If there is any existing data in the cells where the separated 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 data separation and leave the cells as they were, click CANCEL