Extracting email addresses

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.

For examaple:

foo@domain.com [foo@domain.com]

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.

=FIND("[",A1)

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:

=ISERROR(FIND("[",A1))

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 IF statement.

=IF(ISERROR(FIND("[",A1)),A1,"do something")

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.

=MID(text,start_num,num_chars)

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.
The final MID function looks like this.

=MID(A1,FIND("[",A1)+1,(FIND("]",A1)-(FIND("[",A1)+1)))

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.