Separating Text in Excel (Separating 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

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.

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.

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)]
   OR
1. In the Destination text box, click COLLAPSE DIALOG
2. Select the cell(s) where you want the separated data to appear

c.       To return to the Convert Text to Columns Wizard, click RESTORE DIALOG

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

Related Posts by Categories