Chiropractics for Excel

Knead and pound numbers

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!
  
  
• Formatting SSN
  
  
• Inserting hyphens
  
  
• Fixing up names
  
  
• Lower case E-Mail Address
  
  
• Format Home Phone
  
  
• Inserting parenthesis & hyphens
  
  
• Trim extra spaces from Address
  
  
• Add City values to the new table
  
  
• Make all State value upper case
  
  
• Fill in Postal Code
  
  
• Finalize the values

Manipulate and massage  



See all Topics