Image Source: http://www.aquamistcarpetcare.ca/wp-content/uploads/2015/03/do-it-yourself-cleaning.jpg

“I’m in Marketing. MS-Excel isn’t my strength. How do I clean up this database?”

Let’s face it. We’re marketing gurus. Creative ideas, visualization, identifying gaps, analyzing problems, understanding customers – these are our strengths. Not spending hours in MS-Excel, trying to figure out how to clean a database. Unfortunately, with email marketing, there will always be a time when a messed up database reaches your inbox and you’re expected to fix it. Instead of running around, looking for a colleague with impeccable Excel skills, get your hands dirty and solve the following problems BY YOURSELF!

5 Common Email Database Problems and how to solve them

Problem 1 – The first and last name are in the same column

When we want to say Dear John or Dear Mr. Smith and not Dear Mr. John Smith, First and Last names must be in separate columns.

Solution: Use the ‘Text to Column’ function

1. Select the column with names
2. Click on Text to Column function located in the Data tool bar
3. Select ‘Delimited’ and then click on Delimiters: Space
4. Press Finish
5. Excel will split the names in to 2 separate columns

Problem 2 – Checking lowercase and uppercase in names

It’s very common to see some names as John Smith, rob Baker, chris ford, Jane doe in the same list. You need to ensure every first and last name starts with a capital letter. Thankfully Excel has the PROPER() function to solve this.

Solution:  Select a cell which is next to the first name in your name column.

Type =PROPER(choose the cell location of the first name). Press enter. By clicking on the bottom right corner of the cell, all the other names will also be fixed.

Problem 3 – Messed up email IDs (‘@’ or ‘.’ is missing)

Sometimes our database has something like this:

tracy.janegmail.com or

john.smith@yahoocom

Solution: Custom Filter and manually edit those email IDs

1. Select the column of names. Add a data filter by clicking on the filter funnel image in the Data toolbar
2. Click on the filter dropdown and find Text Filters/Custom Filters
3. Within Custom Filters, select Does not contain @ or Does not contain . (full stop)
4. Once your filtered list appears manually correct the incorrect entries
5. Save and then remove the filter

Problem 4 – Messed up email IDs (extra spaces in IDs)

Let’s say some email IDs look like this:

Case 1

Email ID: john smith@gmail.com

Solution: Use Find/Replace Option

Look at the other IDs to see if the company puts a dot in between first and last names or has no space between the 2. It will give you an idea on how to proceed.

1. Select the column of names
2. Press Ctrl + H to open the find replace box
3. In the find box, press SPACEBAR once
4. In the replace with box, put a fullstop (if first and last name is separated by a fullstop) or leave it blank
5. Click on Replace All

Case 2

Email ID: john.smith @gmail.com

Solution: Use the Find/Replace Option

1. Select the column of names
2. Press Ctrl + H to open the find replace box
3. In the find box, press SPACEBAR once then type @
4. In the replace with box, put a @
5. Click on Replace All

Problem 5 – Two Email IDs in the same column

Let’s say your email column has this:

john.smith@gmail.com, john.smith@yahoo.com

Solution: Use the ‘Text to Column’ function

1. Select the column with multiple IDs
2. Click on Text to Column function located in the Data tool bar
3. Select ‘Delimited’ and then click on Delimiters: Comma
4. Press Finish
5. Excel will split the 2 email IDs in to 2 separate columns and remove the comma in between

A clean database has its advantages. Your email will reach a larger target database on account of correct email IDs. Your ability to personalize your email content will be stronger. Also keep in mind, people easily take offence to any issue with their names. A wrong lowercase/uppercase in a name can make all the difference between wanting to read an email or simply deleting it.