Text to Columns

Microsoft Excel 2010

If your worksheet contains information in a single column that you would like to divide into multiple columns, you may be able to split it into separate columns if there is a common deliminator (separator). Huh? Let's say your worksheet contains a column that includes customers' first AND last names in the same column, e.g.

You can divide the information into multiple columns, as follows:

  1. Select the cells you wish to divide.
  2. Display the Data Ribbon and click on the Text to Columns button. The following window appears:
  3. If the data is separated by a character, such as a comma, tab, semicolon, space or other character, choose Delimited. If the data to is a common number of characters wide, choose Fixed width.
  4. Click on the Next button to continue.
  5. If you chose Delimited in step 3, the following window is displayed:
    1. In the Delimiters section, select the character that separates the data. In the above example, that would be a space delimiter.
    2. Notice the effect (of separation) in the Preview area. Choose Next.
  6. The following window is displayed:
    1. Select a column (by clicking once on the header at the top of the column) then select the Column data format from the list provided at the top right of the window. If specifying a date column, use the drop list button to select your preferred date format. You can also use the Advanced button to specify the decimal and thousands separator and format for negative numbers.
    2. You can always apply formatting after you've separated the data.

    3. In the Destination field, specify the first cell to receive the resulting data, if different than shown.
    4. Choose Finish.

Additional References