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 it’s reputation with Comcast.

Here’s the simple trick that allows me to get an ordered list of of my subscribers email clients. This is a tricky task because when Excel sorts a column of email addresses it will stack 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 them paste back into excel to break the formating 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!