Some days, I spend a surprising amount of time massaging data in Excel in order to move them from database to another.
On this round, I was working with names (sometimes all capitals, sometimes not), plus the original source had the name in one field while my new database had them split between two fields. These little tips helped me clean up my data:
I. Splitting Names into Two Columns
Originally from Laura J’s Blog
1. Add two blank columns AFTER the full name. Add three columns if middle names/initials are included.
2. Go to the Data » Text to Columns
3.Check the Delimited Option, then check your name seperator
a. Check the space option if the order is “FirstName LastName”
b. Check the comma option if the order is “LastName, First Name”. If you still have middle names in the second column, you might have to do another split based on space.
II. Changing Cases
These three formulas will let you fix inconsistent capitalization.
·=Proper(A1) – Capitalizes just the first letter of whatever is in Cell A1
·=Upper (A1) – Capitalizes all letters in whatever is in Cell A1
·=Lower(A1) – Changes all letters to lower case (this is great for cleaning up e-mail addresses)
To use the formulas
1. Insert blank columns and use appropriate formulas to convert the names
2. After you finish your conversions, you have to convert the formulas to values if you want to delete the original messy columns. To do that:
a.Copy contents in new column (the formulas)
b. Go to Edit » Paste Special. Select the Values option. This converts formulas to their values.
Now you can delete original messy columns and keep pristine newly formatted columns. This was definitely faster and less error-prone than retyping names from scratch. Thanks Laura J and Microsoft.