Recently I had to send an email newsletter to many recipients. The email addresses supplied were in a single column of a spreadsheet. My mass email sending application uses a comma separated list of addresses. Not usually a problem, a simple CSV export of this list does the job. Only in this case some email addresses were not in a valid format.
I needed a quick way to identify each of these non-valid addresses and manipulate them into a usable format.
Using Excel, I came up with the following formulas to check and correct each bad address.
Step 1 – Identifying a bad address
With one address in each cell starting in cell A1, I placed the following formula in B1 to discover if A1 contained an opening square bracket.
If A1 does contain a bracket, then B1 returns the numeric position of the first occurrence. If there is no bracket, then an error is returned. To avoid this error we can place an
ISERROR statement around the
FIND statement as follows:
Now the B1 cell will return either TRUE or FALSE. Therefore if the cell in question does contain a square bracket, then there is no error and we need to do something, otherwise we’ll be happy returning the valid email address. We can do this with the
Step 2 – Locating the email address
The way I have chosen to return the email address is by extracting the string from inbetween the two square brackets. To do this I used the
MID function. This allows me to return the characters from the middle of a text string, given a starting position and length.
The first property of this function is easy, A1. For the second part I need to find the opening bracket. This can be done by using the previous function
FIND. As this returns the location of the bracket, I need to add 1 so the bracket itself is not returned.
For the last property (num_chars), I’ll use
FIND again, but as this property is a count and NOT the end position I need to subtract from the position of the first bracket.
MID function looks like this.
Step 3 – Joining the two functions together
Copy and paste the
MID function in place of the “do something” statement (whilst remembering to remove the
= sign). The final statement looks like this:
=IF(ISERROR(FIND("[",A1)), A1, MID(A1,FIND("[",A1)+1,(FIND("]",A1)-(FIND("[",A1)+1))))
Step 4 – Apply to all rows
Now we simply double click the little square in the bottom-right corner of the A2 cell to copy this formula to the rest of the rows containing data. Then, copy column B paste the values into a new worksheet and save as a CSV.