How to remove trailing full stops from email addresses

In modern business it is important that we communicate effectively with potential and existing clients. When times are more difficult, it becomes vital. One of the increasingly popular methods for doing this is through e-communications, such as 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.

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

RIGHT(hello,1) gives us o,

whereas,

RIGHT(hello,4) gives us ello.

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:

=IF(RIGHT(A4,1)=".","FULL STOP","").

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

LEN(hello) gives us 5.

LEFT reads the characters from left-to-right, for instance,

LEFT(hello,2) gives us he.

The combined function we type into the cell is:

=IF(RIGHT(A4,1)=".",LEFT(A4,LEN(A4)-1),A4).

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 by calling 01608 737653.

You can download our sample mailing list with the spreadsheet tip in place by clicking here. Always remember to virus-scan any downloaded files.

..back to top