Are you trying to personalize your email campaigns yet frustrated with having to clean up email lists that contain improperly formatted data? You are not alone!

When I founded my email marketing company, I had done approximately zero work in Excel except for updating a marketing calendar that my boss designed to track deadlines. Managing data was definitely a weakness.

So on the day that I count as my very first day in business as Katey Charles Communications (February 20, 2003, in case you are curious), I took an eight-hour class in Excel basics at a local community college. It was exciting to begin to learn the power of building spreadsheets and formulas and pivot tables. There is no end to what you can do with data in Excel, and that can be daunting to those of us untrained in advanced data manipulation.

But for the email marketer who sits in the hot seat day after day, building emails, importing lists and hitting that “send” button, there’s usually no time for a class on Excel formulas.

In these four short videos, we show you three powerful Excel tricks to help you clean up your list and format it with ease so you can send personalized messages with confidence.

Excel Trick 1: Text to Columns

Splitting text from one column into multiple columns

The text to columns feature in Excel will split multiple text elements from one column into separate columns for each element. This is nifty when we want to personalize email messages by first name, as we often need to split first and last names into two separate columns prior to importing into an ESP. Here is how to do that (1.5-minute video):

Another great use for text to columns is splitting out recipient domains for a list of email addresses. Once the domains are listed in a separate column, we can sort easily to analyze which domains are the most popular for our clients’ subscribers. Here is how that works (2-minute video):

Excel Trick 2: Concatenate

Combining text from multiple columns into one

Now, what happens if you have data in two or more columns that you want to combine into one column? You can concatenate them. (I just like saying this word because no one else on earth knows what it means!) For example, let’s say you are personalizing your email campaign using subscribers’ full names, including the appropriate salutations. In your Excel spreadsheet, you have separate columns for first name, last name and salutation. You’d like to combine them into one column. Here is how you can do that (2-minute video):

Excel Trick 3: Proper

Converting improperly capitalized names to the proper case

Another frequent challenge for email marketers is receiving improperly formatted names. If you receive data with names in all caps or first names lowercased, or any mixture of cases, using Excel’s proper function can easily convert your name data to the proper case so that you can personalize messages using correctly formatted names. (One note of caution here: If you have names that should include mixed capitalization, this function will still standardize all of them to only the first letter being capitalized. For example, if you have the last name McNeil in your list, Excel will change it to Mcneil, so be aware of any such names on your list.) Here’s how the proper function works (1.5-minute video):

At Katey Charles Communications, we love to share the email tips and tricks we’ve learned along the way. Contact us with your questions, whether it’s about email strategy, template design or even a Microsoft Excel question!