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
See all Topics