extract or sort on part of an email

**krafty888** · 04-21-2009, 08:25 AM

Greetings all. I have used excel often, but am FAR from being proficient at it. What I have to accomplish is to edit a really long list of email addresses. I need to extract emails with certain parameters after the @ symbol. For instance - extract all emails that contain .on.ca. I have no idea how to do this. I have been hand editing the list but I'm going CRAZY.

Any help MOST appreciated.

**NBVC** · 04-21-2009, 08:32 AM

=If(isnumber(search(".on.ca",A1)),A1,"") copied down... adjust reference to suit.

copied down will extract all emails that have .on.ca in it to the column the formula is in.

**mpquin** · 04-21-2009, 08:52 AM

If you have multiple items that you are searcing for you can extract the characters after the "@" using:

=MID(A4,FIND("@",A4)+1,30)

Then sort/find etc.

**krafty888** · 04-21-2009, 08:59 AM

Awesome and quick response. Thank you.

I got the formula entered properly, and an getting a#VALUE response. I really don't know much about excel - where in the formula do I select a range of cells to sort on, I start at A1 and go to A20643? What is #VALUE telling me?

Thanks again.