Back in December I wrote a post about combining the contents of two cells into one cell.  But what do you do when you need to break the contents of one cell over multiple cells?

Let’s say you have a column for Full Name, but you need separate columns for first and last names.  Should you create a new column, cut the first name from the Full Name column, then paste it into the new column and label that column First Name?

Not unless you really like cutting and pasting and have time to burn. Instead, use the Text to Columns feature in Excel.

Text to Columns separates the contents of a cell into multiple columns based on either a delimiter (something like a space, comma, hyphen, or tab that marks where the next column should begin) or based on a fixed column width (like if you need to isolate the first two characters in a product ID).

Text to Columns Starting Data

Use Text to Columns

1. Insert empty columns to the right of your original column to prevent other data from being overwritten by information from the first column.  Determine the number of columns you’ll need by scanning your data.  It will be one less than the number of columns your data will be separated into.
2. Highlight the original data that you want to separate into columns (A2:A7 in the example above).
3. Go to the Data tab on the ribbon and click on the Text to Columns button.  Or, in Excel 2003, go to the Data menu and select Text to Columns.
   

   Text to Columns button in Excel 2007

4. In the dialog box that opens, select Delimited (unless you are doing a fixed width split, in chich case, select Fixed Width). Click Next.
5. Select the box next to your delimiter (a space, in the example above).  Unselect other boxes if necessary. (If you are using a fixed width, click on the ruler where you want the columns to be divided and a line will appear marking the start of the new column. Double click on a line to delete it). Preview the column breaks in the section at the bottom of the window.  Click Next.
   

   Step 2 of the Text to Columns Wizard with Delimited Selected

6. Select a cell format in the Column Data Format section, if you’d like.
7. In the Destination box, you can select a destination for the data.  If you do not select a destination, the data overwrites the data into the current column and the columns to the right of it.
8. Click Finish.

Text to Column Results

After Text to Columns

Some data cleanup is almost always required after using Text to Columns.  In this case, John Quincy Adams was split into three columns because his full name contained two delimiters (spaces).  Some cutting and pasting will be required to either separate all names into three columns, or combine his name into two columns.