Google Sheets filter out emails based only on the email domain

Viewed 479

I want to filter out all emails with a certain a domain (like @yahoo.com) from a google sheet email list, based on a list of email domains to filter out.

Let's say Sheet1 looks like this (the list I want to filter):

tod     bose    tbose@sink.com  
william small   wsmall@sink.com 
Kafka   snail   ksnail@sink.com 
John    Bose    Jbose@sink.com  
Aug     Smith   asmith@yahoo.com
Aaron   Moog    amoog@googol.com

Sheet2 looks like this (The exclusion filter criteria (I want to exclude these from the list on Sheet1)):

*@sink.com
*@yahoo.com

And Sheet3 is using a standard Google Sheets filter like this:

=filter(Sheet1!A1:C5, ISNA(match(Sheet1!C1:C5,Sheet2!C1:C11, 0)))

And the filtered Sheet3 (the filter has not worked) looks like this (this should show the resulting filtered list):

tod     bose    tbose@sink.com  
william small   wsmall@sink.com 
Kafka   snail   ksnail@sink.com 
John    Bose    Jbose@sink.com  
Aug     Smith   asmith@yahoo.com
Aaron   Moog    amoog@googol.com    

How do I do this so it accounts for the wild card / or does something similar? How do I actually exclude all the emails based on the domain only?

I tried =filter(Sheet1!A1:C5, search(Sheet2!C1:C5,Sheet1!C1:C5)) (and removed the *'s, but to no avail.

**Edit: I want to be left with just the googol email here, I want to filter the domains on Sheet2 out.

Here's the Googlesheet: https://docs.google.com/spreadsheets/d/1R08Srsal54hH42VgM7AJ2mAMYRKNOONLD2Yvzf3n__g/edit?usp=sharing

Thanks.

1 Answers

try:

=FILTER(A1:C, REGEXMATCH(C1:C, ".*@sink.com|.*@yahoo.com"))

enter image description here


reverse:

=FILTER(A1:C, A1:A<>"", NOT(REGEXMATCH(C1:C, ".*@sink.com|.*@yahoo.com")))

enter image description here

Related