Sorting Newsletter Subscribers by Email Client in Excel

Posted by bkloss | Analytics,Email Marketing | Thursday 17 January 2008 5:43 am

I find myself sorting lists of email subscribers every day. A unique situation occurs when I need to sort a list by email client located after the @ in the email address. You can use a combination of the mid and find functions in excel to pull out the client email string.

For instance, I received an email from delivery monitor the other day explaining that roadrunner, a subsidiary of Comcast was discontinuing several domains such as @sport.rr.com. Instead of letting the newly discontinued subscriber addresses bounce, I wanted to clean them from my list preserving its reputation with Comcast.

Here’s the simple trick that allows me to get an ordered list of of my subscribers email clients.  When Excel sorts a column of email addresses it will sort alphabetically by the first letter, I need to tell excel to return all the characters starting from the @ symbol. To do this we have to couple the find function with the mid function.

Here is the formula:



A: Mid function tells excel to return a string in the middle of the cell. It requires 3 pieces of information. The first, A2 designates the cell that the string is contained within.

B: The second argument utilizes the find function because the start point of string we want(the email client) comes after the @ symbol and varies in position across email addresses. For instance, bobby@gmail (Gmail starts at position 7) and bob@gmail (Gmail starts at position 5). So, the second argument says find @ in A2 and return the string starting at position @+1.

C: designates the end of the string. I use 7 characters as a rule because it will return a string large enough to be recognized at a glance.

If you implement the formula correctly it will return column B as shown below:

Because Excel stores column B as numbers it is necessary to copy column B into a text editor then paste back into excel to break the formatting and end up with something that is sortable.

Here are some questions you can answer when you can sort your subscriber list by email client.

  • What is the distribution of different clients across my list? Is it composed mostly of free addresses like gmail or yahoo?
  • Are open rates and click-throughs consistent across email clients? If not, this may suggest that you have a deliverability problem.
  • Conversely, if you’re asking copywriters to gut marketing messaging so you hit the inbox of sbcglobal subscribers, you should know what % of your list they make up (if it’s .0001% leave the copy be! :) )

You get the idea. There are 1000 and 1 applications for this technique. If you discover a particularly useful one, please share it as a comment on the blog.

Enjoy!

4 Comments »

  1. Comment by Stephen Bolts — September 15, 2008 @ 1:47 pm

    That’s really useful – I need to sort an outdated contact list and this will help me do just that, would never have realised I could do that with Excel – many thanks.

  2. Comment by Orlando — February 10, 2009 @ 6:39 pm

    This is a great formula, I was able to extract all of my AOL contacts from an email list by just putting the formula in the cell and hitting return and dragging the cell down to the last email. Kudos Beck, I can envision other useful task for this tool.

  3. Comment by sebastian — April 10, 2009 @ 4:57 pm

    excellent. thanx… you saved me a lot of time…!
    sebastian

  4. Comment by Tom — July 12, 2010 @ 8:14 pm

    Nearly cried when I saw that I had to sort 2100 company contacts…this saved me hours! thanks a lot haha

RSS feed for comments on this post. TrackBack URI

Leave a comment