Cleaning up your mailing list: a few tricks in Microsoft Excel

It’s annoying, really annoying. Especially when you are on the receiving side. And it’s definitely reflecting on the brand image of the sender: errors in mailing lists. And I know: I see them on a regular basis. Two mailing pieces at the same moment, wrong use of capitals, wrong language, even assigning me the wrong gender. For some common errors in mailing lists, a few simple tricks in Microsoft Excel can already help you. Let me show you how.

CONTENTS: Uppercase, lowercase, proper case | Doubles | Language dependent | Let’s get serious… | The G-word… | Why is this important? 

Just to give you an idea how often I see errors in mailing lists: the past week I got a mailing in the wrong language (in French instead of Dutch). I got one for a very special promotion for a software upgrade, which I had already purchased from their website about a month ago. And I got a ‘last chance’ invitation for a seminar that I already had attended a few weeks ago… And if I look a bit further back, I’ve received two pieces of the same mailer, on several occasions. And one brochure was addressed to miss Eddy Hagen (I checked it before starting writing this blog post and I’m definitely male! ? ). And the same company often shouts at me in their emailings: ‘EDDY, now is the right time!’. And all of these mistakes are very annoying. They could, and should, have been prevented from happening. 

Uppercase, lowercase, proper case
Let’s start with an easy one: how can you – automatically – adjust the use of uppercase/lowercase in Microsoft Excel. Well, Excel has the following functions: UPPER, LOWER, PROPER. ‘Upper’ will transform all the letters into uppercase, ‘lower’ into lowercase and ‘proper’ will give every word a first capital letter. So ‘proper’ would transform EDDY HAGEN into Eddy Hagen.

If you have a mailing list exported to Excel, you can easily transform a column with names. Suppose all the names are in the B column, with B1 being the column title, you go to cell C2 and use the following formula: =PROPER(B2). And then you extend that formula to the rest of the column (you can do that by grabbing the ‘+’ sign at the right bottom border of the cell and dragging that to the last cell you need).

Using this function in Excel is, of course, a ‘quick and dirty’ way of doing this. And you need to be careful, especially in some parts of the world. In the region where I live, some last names have a lowercase d (d’, de or du) or a lowercase v (van or von). And certainly the ones with a lowercase ‘d’ are sensitive to it: they are usually a sign of nobility. E.g., the last name of the queen of Belgium is d’Udekem d’Acoz. In these cases, manually checking it wouldn’t be a bad idea. Many are very fond of their lowercase d…

If the formula above made changes, you probably want to know which names have changed, so that you can also correct them in your database, your CRM system. There is also an easy way to check it, via the following formula, where the original values are in the B-column and the ‘proper’ values in column C: =IF(EXACT(B2;C2);””;”CHANGED!”) The formula consists of different parts. The ‘IF’ argument indicates that we want to check a certain condition: if cells B2 and C2 are exact the same, including the case. After that, first the message (“”, which is an empty string) is shown when the condition is true, the second message (“CHANGED!”) is shown when the condition is false.

Doubles
A very costly error in mailing lists is double entries. Especially if you are mailing physical pieces: there is the production cost, plus postage cost. To check for doubles, we are first going to sort the Excel sheet with mailing addresses. And we are doing this first by postal code, then by address and then by the last name.

There is a reason why you should only use the postal code for this sorting and not the place: in some cases, different place names can be used for the same postal code. I’m e.g. living in Deurne, which is a suburb of Antwerp, the postal code is 2100. You can use both Deurne and Antwerp in my address, no problem. The unique identifier is the postal code, not the place. That’s the reason why you should only use that one in this exercise.

Now that we have sorted the addresses, we will add another formula to check for doubles. Which is quite easy, if you know how: check if the last name in cell C3 is the same as in cell C2. If that’s the case, the message DOUBLE! will appear: IF(C3=C2;”DOUBLE!”;””).

There are two flaws in this method – that’s why it’s only quick and dirty. The first is when a family is sometimes only using the husbands name (e.g. Hagen) and sometimes using both names (e.g. Hagen-Dessein). These are not the same, so Excel will see them as different, even though it’s the same family. So this is something you might want to check manually. A way to facilitate this, is to perform the check we just had to the cells with the address: it will show a note when the address is the same, meaning that you should check it.

The second flaw is with street names that can be written in multiple ways. E.g., in Antwerp, there is the Jan Van Rijswijcklaan. You can use Jan Van Rijswijcklaan, J. Van Rijswijcklaan and Van Rijswijcklaan; the postal services won’t mind and deliver the mailer. But your database does mind and will see it as different streets: you should pay attention to that when entering data! 

Language dependent
There is a chance that the formulas I’ve given you don’t work on your system: the formulas in Microsoft Excel are language dependent… But here’s a great tool, a formula translator, to translate the formulas from English into your language. Let’s look what the formulas look like when translated into Dutch, my native language:

=PROPER(B2) becomes =BEGINLETTERS(B2)
=IF(EXACT(B2;C2);””;”CHANGED!”) becomes =ALS(GELIJK(B2;C2);””;”CHANGED!”)
=IF(C3=C2;”DOUBLE!”;””) becomes =ALS(C3=C2;”DOUBLE!”;””)

UPDATE 15/11/2018: I just discovered that you now can use an add-in for Excel which will translate formulas. Please note: it’s available only for Office365, Excel 2016 and Excel 2019. 

Let’s get serious…
If you have reached this point, you are serious about cleaning your database, which is very good! But, let’s get serious… doing this in Excel is not the best way to do this. There are many tools available that will make it much easier and the ROI is very short, especially when you are sending out physical mail pieces.

Next to checking for doubles, many services also offer a very interesting tool: address verification. This has two advantages: first is that you are sure that the address really exists… And I can tell you the anecdote of someone who always uses a fake address when he has to give his address for promotional reasons… And the other advantage is that addresses will always be in the same format. Which makes it easier to check afterward, e.g. for double entries.

Experian Data Quality e.g., offers a free address lookup tool, which works for most countries in the world! 

Multiple websites offer data cleaning or verification services and they offer it for about every country in the world. To give an idea of the price level, Experian Data Quality charges between 0,04 US $ (<10K, min 50 US $) and 0,0075 US $ (< 500k) per record. SmartyStreets offers a subscription plan starting at 7 US $/month for max 100 lookups per month. With the cost of a mailing piece in mind, this is not expensive. Search for ‘address validation service’ and you will find a lot of companies that provide this. 

The G-word…
And in case you are wondering why the time is right to clean up your database: GDPR. The General Data Protection Regulation in the EU, which every company dealing with European customers has to comply to by 25 May 2018. Getting to compliance does take some effort, combining it with database cleaning makes sense and will probably save you money in the end. 

Why is this important?
As I already said, errors in mailings are very annoying. It leaves (potential) customers with a bad feeling: “if that company doesn’t even take the effort to spell my name right, why would I want to get into a business relationship with it?” From a sender point of view, this might not look critical, but from a receiver point of view, it is. Very much. You need to invest time to get the most basic information about a customer right. That’s the least he/she deserves before you can try to convince him/her to buy products from you.

PS: today PC World also published an article showing some tricks you can perform on texts when using Microsoft Excel.

Related posts:

First impressions matter: good data hygiene is key for direct mail, good print quality too X-Rite Color-Eye: color identification, not color measurement How much should a printing company invest in marketing and sales? Vistaprint (Cimpress): 30% and more!