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

return to topConsiderations

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.

return to topSplitting the Data

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

  2. From the Data menu, select Text to Columns...
    The Convert Text to Columns Wizard - 1 of 3 dialog box appears.

  3. Under Original data type, select Delimited

  4. Click NEXT

  5. Under Delimiters, select the character(s) you want to use to separate the cell data

  6. OPTIONAL: Select or deselect Treat consecutive delimiters as one as appropriate
    NOTE: For more information, refer to Considerations.

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

  8. Click NEXT

  9. 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 DIALOGCollapse Dialog box button
      Macintosh: In the Destination text box, click COLLAPSE DIALOGCollapse Dialog box button
      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 DIALOGRestore Dialog button
      Macintosh: To return to the Convert Text to Columns Wizard, click EXPAND DIALOGExpand Dialog box button

  10. Click FINISH

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

Excellence. Our Measure. Our Motto. Our Goal.