Thursday, February 4, 2016

Text to Columns Revisited

A couple of years ago, I did a post on the Text to Columns tool in Excel.  Judging by the number of hits it has received, its popularity bears taking another look.

The Text to Columns tool is particularly worth discussing further, as it is highly useful and often overlooked by even longtime Excel users.
As any analyst knows very well, data isn’t always presented in the most ideal formats for use in Excel.  Data may come from a variety of robust data storage sources, or even from such unlikely places as Word documents! 
Here is an Example of How the Text to Columns Tool Works:

To illustrate, we will use the following string of numbers (intentionally rudimentary…) that you may find in a Word document or any other numerous sources:

14, 22, 36, 35, 64, 34, 28, 94
  1. Select the string, copy it, and paste it into a cell in Excel (in this example, A2 was used)
  2. Select the cell and click on the Text to Columns icon on the DATA ribbon
  3. The Convert Text to Columns Wizard will appear giving you the following two major options:
    • Delimited – Characters such as commas separate each field
    • Fixed width – Fields are aligned in columns with spaces between each field
  4. In our example, we choose Delimited since our numbers are separated by commas
  5. Following the steps in the Text to Columns Wizard gives you the choice to pick your desired format:
    • General
    • Text
    • Date

It’s as Simple as That!  It really is that easy to get your data into Excel in proper alignment and format. Have no doubt about it, Text to Columns is a very worthwhile gadget for any Excel user to have in their tool belt. Give it a Try!

1 comment:

kabilan said...

I am into a type of job that requires data to be transferred from MS Word to MS Excel. Often I was faced with the challenge of posting text data into a columnar format. But after reading the blog by Bob, I saw what wonders MS Excel can do! I joined an online Excel course and now with the help of all Excel tips and shortcuts, my job has become very simple!