Forum Discussion

Copper Contributor

Jan 17, 2024

Solved

Filter Advanced / Multiple criteria / non contain

Hi All I need to filter out from the column A (a list of emails) all the non business emails. In the column D I have the criteria (private emails to be excluded). In the real case both column ...

excel

djclements
Jan 17, 2024
MarcoTorinoi Please see the attached workbook with the list of email provider domains arranged horizontally. With such a huge criteria list, performance may be an issue when applying it to a large dataset. You'll just have to test it to see how well it performs...
DoesNotContainDomainList.xlsx93 KB

OliverScheurich

Gold Contributor

Jan 17, 2024

MarcoTorinoi

=IF(SUM(N(ISNUMBER(SEARCH($D$2:$D$4,A2)))),"",A2)

A simple off-the-cuff alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

If you work with a recent version of Excel you can apply the FILTER function.

MarcoTorinoi

Copper Contributor

Jan 17, 2024

OliverScheurich

thanks..but unfortunatelly the formula does not work. you see any error?

OliverScheurich
Gold Contributor
Jan 17, 2024
MarcoTorinoi
=IF(SUM(N(ISNUMBER(SEARCH($D$2:$D$3,A2)))),"",A2)

There are 2 criteria in your example in range D2:D3 therefore the formula has to reference D$2:$D$3.

Additionally since you don't work with Office 365 or Excel for the web or Excel 2021 you have to enter the formula as an arrayformula with ctrl+shift+enter. This will put the curly brackets { } around the formula. You can see the brackets highlighted in yellow in the screenshot from my Excel 2013 worksheet.