It is important that we communicate effectively with potential and existing clients. One of the increasingly popular methods for doing this is through e-communications, such as blogs and newsletters. Our clients often ask for assistance in tidying e-mailing lists for upload to their e-marketing provider. Over the next three months we will look at some of the most common ways that mailing lists cause problems, and how to quickly clean these up in Excel. First, we look at a quick way of removing those annoying full stops that seem to attach themselves to the end of email addresses, causing your message to be bounced.
Removing a trailing full stop from email addresses.
Purchase the Mailing List Preparations Spreadsheet Example
Click the button below to purchase the Mailing List Preparations Spreadsheet Example
Once you have completed your purchase on PayPal you receive the download link by email.
Follow the Mailing List Preparations Spreadsheet
One of the most common mistakes with e-mailing lists is a full stop on the end of the email address. This is really easy to miss, as we are used to putting full stops at the end of each sentence, and if you are copying and pasting rows from one sheet to another, it is not always easy to spot. Spreadsheet Data 1 shows a small sample mailing list with some correct and incorrect entries.
Spreadsheet Data 1
The second and third rows have a full stop at the end of the email address. The first step is to identify which email addresses have a full stop at the end. We use two functions to do this, IF and RIGHT.
RIGHT reads the content of a cell, reading from right-to-left. We also tell Excel how many characters to read. For example
IF examines a statement and then takes action depending on whether the statement is TRUE or FALSE.
In this case, we want to know if the first character reading right-to-left is a full stop or not. The function we type into the cell is:
In plain English this reads as; If the first character reading from the right is a full stop, then show the text “FULL STOP”, if not, show nothing, i.e. “”. See Spreadsheet Data 2.
Spreadsheet Data 2
If we copy this formula down for all the rows we see that Excel has identified the 2nd and 3rd rows as requiring action. See Spreadsheet Data 3.
Spreadsheet Data 3
We can take this a step further and make Excel give us the email address with the full stop removed. We use the same premise, asking Excel to find out if there is a full stop at the end of the text, and using an IF statement we direct as to how to proceed. For this formula we also use the LEN function and the LEFT function. The LEN function determines the number of characters in the email address. For instance
LEFT reads the characters from left-to-right, for instance,
The combined function we type into the cell is:
In plain English this reads as; if the first character reading from the right is a full stop, then show me A4 but with one character removed, reading from the left, otherwise show me the original A4. See Spreadsheet Data 4.
Spreadsheet Data 4
If we copy this formula down for all the rows, we see that we now have a tidy column of complete email addresses with any full stops at the end removed. See Spreadsheet Data 5.
Spreadsheet Data 5
Of course, getting your mailing list in good order is only part of the process. You need to be able to get your message across effectively. You can talk to Kate Barlow at Branchout Internet Marketing to see if your campaign is going to be any good!
You can build your own Mailing List Preparation Spreadsheet Example by following this spreadsheet tip, but if you would you like to take advantage of the time we’ve already spent on it, purchase the Mailing List Preparation Spreadsheet Example by clicking the button below.
After completing your purchase via PayPal you receive a download link by email. The Spreadsheet Example demonstrates how to remove trailing full stops, swap commas for points and how to build salutations.