This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007/2008

Separating Text

Excel's Text to Columns feature allows you to separate the text that is contained in a single cell and split it into multiple columns. For example, a full name appears in one cell (e.g., Doe, John). You can use Text to Columns to put the last name in one column and the first name in the next column. This would make it easier to use in a mail merge.

Another example would be city and state information appearing in one cell (e.g., Eau Claire, WI). You can use Text to Columns to put city names in one column and state abbreviations in another. This makes sorting by state easier.

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 using custom delimiters.

return to topConsiderations

Here are some things to consider before separating your cells:

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 blank columns to the right of the column you are separating. For more information on adding columns, refer to Working with Rows and Columns (Win | Mac).

return to topSeparating the Data

  1. Select the cells containing the data you want to separate
    NOTE: Excel can only separate one column at a time.

  2. Windows only: Select the Data command tab

  3. Windows: In the Data Tools group, click TEXT TO COLUMNS Text to Columns button
    Macintosh: From the Data menu, select Text to Columns...
    The Convert Text to Columns Wizard dialog box appears.
    NOTE: Your data is displayed in the Preview of selected data section.
    Text to Columns Wizard - Step 1 of 3 dialog box

  4. Under Original data type, select Delimited

  5. Click NEXT

  6. 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 menu allows you to select the symbol you wish to use to indicate that the data included within is to be treated as text.
    Text to Columns Wizard - Step 2 of 3 dialog box

  7. OPTIONAL: As appropriate, select or deselect Treat consecutive delimiters as one
    HINT: For more information, refer to Considerations.

  8. Click NEXT

  9. 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)]
    1. In the Destination text box, click COLLAPSE DIALOGCollapse Dialog box button
    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 DIALOGRestore Dialog button

  10. 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.

  11. To replace the data, click OK  
    To cancel the data separation and leave the cells as they were, click CANCEL
Excellence. Our Measure. Our Motto. Our Goal.