Split data into multiple columns

Split data into multiple columns

After importing data into Excel, you may decide that there is data concatenated into single cells that you want split out into multiple cells. For example, suppose the data you have imported shows the first and last names of the people in a single cell together. Excel has a command that to help you separate it: the Text to Columns command.

The only catch here is that there has to be a consistently used delimiter character within the cell in order for Excel to be able to split it (for a variable length), or a consistent number of characters before the split in each record (for a fixed length).

For example, with the variable length, if a space is your delimiter character, the first name and last name must be separated by a space, and nothing else within that cell can be separated by a space. So if you have anybody with two first names, like Mary Beth Jones, the command wouldn't work correctly on that name. But it can still be a decent time-saver to use Text to Columns and then manually fix the few cells that had a problem with it.

To use Text to Columns:

1. Select the cells to be included.
2. Choose Data > Text to Columns. The Convert Text to Columns Wizard runs.
3. Choose Delimited or Fixed Width.
4. If you chose Delimited, specify the delimiter character, as shown in Figure 6-2.

Figure 6-2: Separating text with a delimiter.

5. If you chose Fixed Width, click to indicate where the break should occur, as shown in Figure 6-3, and then click Next

Figure 6-3: Separating text based on a fixed width.

6. Set a data format for each column, as shown in Figure 6-4. This helps Excel later when you enter additional records into the list. Your choices are General (best in most cases), Text (good for when you'll be having numbers by themselves that you don't want to treat as numeric values), Date (for dates), and Do Not Import (to skip a column). Click Next.

Figure 6-4: Setting a data format for the data.

7. Click Finish. The data is split into multiple columns.