Using Text to Columns to clean up data

The Text to Columns command will allow you to clean up Excel data in a variety of scenarios, such as:

  • Database imports where numbers are being treated as text by Excel.
  • Situations where you need to convert a column of one datatype to another.
  • Problems caused by phantom spaces or characters at the end of cells.
  • Scenarios where you want to "split up" data (e.g. First from last name, WBS levels, etc.)

To use Text to Columns:

  1. Select the column (or cells) you wish to convert
  2. Choose Data → Text To Columns.
  3. Step through the wizard.

    Pay attention to whether you have a delimiter (something that allows you to "split" the data) or whether you are wanting to do a "fixed width" conversion. Also pay attention to the format of the destination column.

    You can put the converted data onto the same column/cells or onto a different range. When in doubt, choose a different target range--it's always easy to cut and paste the results later.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.