As database marketers, we sometimes get faced with the tedious task of cleaning up spreadsheets and uploading lead lists into our marketing automation or e-mail marketing database. In order to better facilitate this, here are a few tips & tricks to make your data imports quick, efficient, and optimized for your e-mail marketing efforts.

1. What do I do when I get a lead list where first and last names are in the same cell?

First, find the column of names you want to separate into different columns. Insert enough columns after this to accomodate for each piece of data. Most of the time you will only need one more column after your original column if your data just contains “First Name” and “Last Name.” However if your data contains middle initials or titles (Jr., Sr., Dr., Mr., Mrs., III, etc.), you may need to add more columns.

Second, highlight the entire column of data you want to separate. Then, find the “Data” tab above the ribbon in Microsoft Excel. Under “Tools” select “Text to Columns.” Select the “Delimited” radio button. On the next step of the wizard, uncheck “Tab” and select “Space” as your delimiter. This will put each word that has a space between the word before it into a separate cell (thus separating first and last name!). In the last step of the wizard, just press “Finish” and all of your data will now be separated into the columns.

Finally, you may have to do a bit more cleanup if your data included more than first name and last name as if there are middle initials some of those might be in different columns to contacts that don’t have middle initials. However, this small cleanup process is much easier than manually separating the data.

2. What do I do when I get a list where names are in all CAPS? I don’t want to sound like I’m yelling at my prospects when I send them a personalized e-mail!

The last thing you want to do is send out a personalized e-mail where your prospects can easily tell that it is automated; or even worse, to sound like you are yelling at your prospects with a “Hey ALISON,” in your greeting line.

One of my favorite Excel discoveries is the “PROPER” function. First, say that you have a column of first names where all the data is in CAPS. Insert a blank column next to it. In the first blank cell of your new column type “=PROPER(A2)” where A2 is the cell containing your first first name in all CAPS. Then simply copy and paste the formula all the way down your blank row. All of your data is now in a “proper” format.

Finally, copy all the data in your now properly formatted column. Select cell A2 (if that is the first cell containing your all CAPS data). Right click and select “Paste Special” and then select “Values.” This ensures that you copy the pure data, not the formula. This is also very helpful for other field types such as “Company Name” or “Job Title.” Basically, any field you would use as a personalization field in an e-mail should be properly formatted prior to import.

3. How do I format all my zip codes to be 5-digits only?

Many times I’ve gotten a list of leads where some of the zip codes are 5-digits and others are 9-digits. This doesn’t matter if you don’t use these fields for specific location targeting, but if you do, you may want to make sure all your US prospects have 5-digit zip codes.

The easiest way to accomplish this is to create a new blank column next to your zip code column. In the first cell of your blank column type, “=LEFT(A2,5)” where A1 is the first cell containing a zip code.

Finally, copy all the data in your column with properly formatted 5-digit zip codes. Select cell A2 (if that is the first cell containing your original column of zip codes). Right click and select “Paste Special” and then select “Values.” This ensures that you copy the pure data, not the formula.