Chad Rothschiller, a program manager on the Excel team, discusses using formulas to 'clean up' data in Excel.
Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another.
This example considers a sample data set and walks through the steps to clean up the data and perform various transformations on the data set to massage it into a more desirable format.
I'm sure you've been faced with at least one of theses problems:
Import the data and don't accidentally drop the leading zeros!