Sometimes a report may include information all in one column, and you really need it to be separate. The most common example would be a column that contains City, State Zip; you need one column for City, one for State, and one for Zip. Rather than doing a tedious copy-and-paste operation, there is a shortcut in Excel that will split it up for you.
- Highlight the column that contains the data.
- On the Data menu, select Text to Columns. Leave Delimited selected, and click Next.
- On Step 2, select the Comma delimiter (unless your column contains another character to separate one piece of data from another). If, for instance, you were separating the First Name from the Last Name, you'd use a Space delimiter. Click Finish when it looks correct.
- Notice that the State and Zip are still in the same column--if you'd used a Space delimiter instead of comma, "Sale City" (a city with a space in its name) would have been split into 2 columns. So now repeat steps 1-2, highlighting the State Zip column first.
- When you get to the Step 2 of 3 screen shot above, instead of using Comma, use Space. Again, click Finish.
- Now your data is in separate columns. You may have a little clean-up to do (deleting the extra column, re-naming columns), but it's a lot faster than re-typing or copy/paste.