Splitting Text into Different Columns
October 15th, 2005This problem seems to come up a lot in our classes: You have a list of names in a column. Each cell contains both the first and the last name.
You need to have the first name appear in one column and the last name in the next column. (Or you have the city, state and zip code in one column and you want to split them up into three columns.) And, of course, you don’t want to re-type the whole list!
Excel has a feature to deal with this problem, but most people don’t know about it. Here’s how to split up the text into different columns:
(Let’s assume the list of names is in column A and there is data in several columns to the right of column A.)
- First, insert two columns to the right of column A. (You really only need one, but like me, you should be paranoid about losing important data. The extra column is a precaution.)
- Select Column A.
- Select Data | Text to Columns.
- The Convert Text to Columns Wizard Step 1 will appear:
- Assuming the first and last names are separated by a space, select Delimited and click Next.
- The Convert Text to Columns Wizard Step 2 will appear:
- In the Delimiters section, check Space, remove other checks, and click Next.
- The Convert Text to Columns Wizard Step 3 will appear:
- In the Column Data Format section, select General or Text, and click Finish.
- Your text should now appear in two separate columns:
- You can now delete the extra column you created as a precaution.
Leave a Reply
You must be logged in to post a comment.