Cleaning up numbers from database imports
Introduction
When importing numbers from databases, all kinds of things can go wrong. This section will discuss some of the options you have for dealing with database-import problems in Excel.
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:
- Select the column (or cells) you wish to convert
- Choose Data → Text To Columns.
- 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.
Using functions to clean up data
The following functions can be useful to 'clean up' data that is 'dirty' after importing:
- TRIM(string): removes leading and trailing spaces (which seems to be a leading cause of data import problems.
- LEFT(string, # of characters): returns the first x characters of a string.
- VALUE(string): converts a text argument to a value.