In this Excel tutorial, you'll learn how to clean up data using the TRIM, PROPER, and Text to Columns functions (and more).
By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers"
Why Do You Need to "Clean Up" Data? Often you've pasted in data from websites or PDFs or other sources, and you get lots of ugly formatting and other problems, such as extra spaces, non-printable characters, etc.
Also, data may be grouped together in cases where it's better to be separated (as in the address data here).
This happens all the time on the job, and cleaning up the data makes your life easier and makes it 100x easier to manipulate and analyze it.
You COULD go in and manually fix it, but you might want to jump off the roof of a tall building after doing that.
Instead, we'll use these functions to automate the process:
Text Manipulation Formulas (Across all PC and Mac versions):
=TRIM Remove extra spaces
=PROPER Makes first letter in each word uppercase
=CLEAN Removes all non-printable characters from text
=UPPER Capitalizes all letters in all words
=LOWER Turns all letters in all words to lowercase
Alt + A + E / Alt + D + E Text to Columns
Ctrl + C Copy (CMD + C on the Mac)
Alt + E + S + F Paste Formulas (Ctrl + CMD + V, CMD + F on the Mac)
Alt + E + S + V Paste Values (Ctrl + CMD + V, CMD + V on the Mac)
Alt + O + C + A Auto-Fit Column Width
Alt + H + C + A Center Text
How to Clean Up This Data in 5 Steps:
1. First, remove all the extra spaces and capitalize each individual word with TRIM and PROPER - could throw in CLEAN for good measure.
2. Then, separate everything into separate columns with the "Text to Column" function.
May have to apply this several times if different characters separate each type of data (commas vs. spaces).
3. Fix anything that still requires fixing in these separate columns - capitalize all state abbreviations, make sure ZIP codes with trailing 0 still work properly (change format to text), and so on.
May also need to apply additional TRIMs here.
Must be really careful with copying and pasting data as values - have to do that to avoid errors!
4. Add column headers at the top, based on copy and paste of original header.
5. Delete extra rows/columns and shift everything over or up properly.
Go apply this to real data that you're working with... depends a bit on the specific problems with the data, but you can never go wrong with TRIM, PROPER, and Text to Columns!
If you're more advanced, you could try automating this entire process with VBA and macros,
but that also gets complicated and may not save you much time since you need to know what the data looks like before writing code for that.