Today, I taught myself how to separate text in an Excel column into two columns with the Text To Columns function. This can be a huge time-saver when you have a lot of data to convert.
I have a spreadsheet with student info…name, email address, etc. The first and last names were in one column separated with a comma. Well, I needed the names in separate columns, and discovered the Text to Columns function. Now, I would like to share it with you.
It’s a good idea to create empty columns for your converted data to the right of the column holding the text you’d like to convert or split (first and last names for me). Then select the column you’d like to split and click Text to Columns on the Data tab on the Ribbon.
When you click the Text to Columns command, the Convert Text to Column Wizard dialog box appears with a two choices…Delimited or Fixed width. Use Delimited if the text in the column is separated with a comma, tab, semicolon, or a space; and choose Fixed if the text is aligned in columns with spaces between each field.
In the next step of the Wizard, you’ll choose the column format, like Text or General; and the destination column for your output. If you don’t choose a destination, the converted text will replace the original text in the column you selected earlier and flow into the column or columns to the right when you click Finish. It’s so easy and it works with Microsoft Excel 2007 or 2010 (Windows) and Excel 2011 (Mac). So cool!
I need to use this function two times a year; so if I forget it, I’ll refer to this blog post to refresh my memory. And,you can do the same.
I hope you found this Excel tip helpful. Post a comment if you have an Excel question for me!
Kathy
Microsoft Excel Online Classes Start Monthly
