Solved Excel 2010- extract a specific email from a list of emails

February 2, 2012 at 09:50:33
Specs: Windows 7
I have a list of emails in Excel 2010 that are like this:

Row L2:joe@first.com,john@second.com
Row L3:mike@second.com,speedy@third.com
Row L4:mary@second.com
Row L5:tomthumb@third.com,moonbounce@first.com,sam@second.com

I would like to extract all of the emails from a specific domain (@second.com). The text to columns just splits them at the delimiter. Any ideas on how to extract based on the domain? Please and Thank you!


See More: Excel 2010- extract a specific email from a list of emails

Report •


#1
February 2, 2012 at 10:41:24
What is the maximum number of email addresses that you might have strung together in a given cell?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 2, 2012 at 11:25:36
Three seems to be the most in anyone give cell. Those are few and far between. Two is the predominate number. Thanks!

Report •

#3
February 2, 2012 at 11:55:31
✔ Best Answer
Pick a cell in which you will enter your desired domain name. For the following formula I chose I1.

Do your Text-To-Columns to split your data across Columns L:N.

Enter this formula in a cell and drag it down. I split it across 3 lines to make it look better in this post. You may need to put it back together.

=IF(NOT(ISERROR(FIND($I$1,L2))),L2,
IF(NOT(ISERROR(FIND($I$1,M2))),M2,
IF(NOT(ISERROR(FIND($I$1,N2))),N2,"")))

What it does is try to find the domain name in Column L, then M, then N, returning the value in that column if FIND does NOT return an Error.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 3, 2012 at 07:12:44
Awesome! That was exactly what I was looking for and it works like a charm. Thanks for the quick help!!

Report •


Ask Question