Newsletter - September '09
|
|
Spreadsheet Tips
|
|
Removing a trailing full stop from email addresses.
|
 |
Spreadsheet Tips - Tidying a Mailing List (1 of 3)
|
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.
Fig 1 shows a small sample mailing list with some correct and incorrect
entries.
|
|
| Fig 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 Fig 2.
|
|
| Fig 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 Fig 3.
|
|
| Fig 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 Fig 4.
|
|
| Fig 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 Fig 5.
|
|
| Fig 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 |
|
|
|
|
