This is part of a series of Excel tips focused on helping you clean up your contact data. Today’s tip explains how to use Excel proper case to convert names from UPPERCASE or lowercase to Proper case.
The other day I was getting ready to send an email to a small group of people. I like to personalize emails, even when sending to a group. So I set up my emails to address everyone by their first name.
Looking at my list of contacts, I noticed that some of the names were all UPPERCASE, and some we all lowercase. Being a bit of a perfectionist, I wanted all the first names to have the same formatting. With names you should use proper casing. That means an uppercase first letter, with the rest of the name in lowercase.
Remember, I was sending this email to a group of people, not one at a time. That doesn’t mean that my message wasn’t personal. I wanted it to come across as being from me to the each recipient personally. I was asking for some specific feedback from this group.
Nothing would throw off that personal feeling like starting the email with Hi STEVE or Hi steve. I had to fix those first names so they would look natural, à la Hi Steve.
Excel is on the Case
Luckily, there is a quick and painless way to change the casing on names (or any text) in Excel.
To do so, use the Excel proper case formula that looks like this: =PROPER(CELL)
Let’s walk through how to do this step by step.
Step 1: Add a Blank Column
You’ll be putting the formula in a new column. I like to insert it right next to the column that holds the data I am changing.
To insert a column, right click on the letter of the column that you want the new column to be inserted in front of. Click Insert and a new blank column will appear.
Step 2: Add the Excel Proper Case Formula to the New Column
Now it’s time to add the formula. Pick the first blank cell in the new column and type in =PROPER(CELL) where “CELL” is the location of the original name.
So, if the original first names are in column A, and the first one is on row 2, your formula would be =PROPER(A2). This converts the contents of cell A2 into Proper casing.
Next, you’ll want to apply that same formula to the rest of the cells in your column. Do this by clicking on the first cell with the formula in it and then double clicking on the little black square in the bottom right corner. You can also click and drag on that box all the way to bottom of the column. This will copy the formula into the rest of the cells in the new column.
Note: The fastest way to do this is to just convert all the first names. It won’t hurt the ones that already have proper casing, and it will save you a ton of time.
Step 3: Convert Your New Column to Text
If you’ve done steps 1 and 2 successfully, you now have 2 columns with first names. If you’re like me, you will want to clean that up. But wait! You can’t just delete your original column of first names.
Remember, your new Proper cased column of names is based on a formula. That formula only works when the original column is still there.
What you need to do is convert your new column to text. But don’t worry, that’s also super easy.
Click on the column letter above the first cell in your new column. This will highlight the entire column. Then right click and choose copy (or hit Ctrl-C).
Next, place your cursor in the first cell of that same column. Right click, and look for Paste Options. Choose to paste as Values. It’s the icon with the 123 on it. You can also just type V once you open that right click menu, and it will select the Paste as Values option.
This overwrites the column with the same content, but saves the actual values rather than the formula result. Now it’s safe to remove the original column of names.
Send Those Personalized Emails
That’s it! You can import that spreadsheet into your favorite tool to send out those personalized emails.
If you are sending emails via Batchbook with our Gmail integration, first save your spreadsheet in CSV format. Then, you can just drag it into any screen in Batchbook to start the import process. If you are updating contacts, be sure to select a merge option. Merging by Just Name will work to update the casing on the names in Batchbook.