Search for multiple words or values simultaneously

[Marceti]

We work with company databases which include large number of emails, and many times we receive databases with personal mails such as hotmail or gmail accounts, or foreign domains (.mx, .it) which we need to delete in order to keep only the corporate local emails.

I tried using "OR" in the search engine ("hotmail OR gmail OR yahoo...") but it didnīt work since Excel only allows for single word search.

Is there a formula that allows for multiple word search simultaneously, and then select or highlight for deletion?

Thank you for your assistance!

[TheMechEngineer]

Re: Search for multiple words or values simultaneously
Quote Originally Posted by Marceti View Post
We work with company databases which include large number of emails, and many times we receive databases with personal mails such as hotmail or gmail accounts, or foreign domains (.mx, .it) which we need to delete in order to keep only the corporate local emails.

I tried using "OR" in the search engine ("hotmail OR gmail OR yahoo...") but it didnīt work since Excel only allows for single word search.

Is there a formula that allows for multiple word search simultaneously, and then select or highlight for deletion?

Thank you for your assistance!
Take a look at this, as you enter from any of the possible emails into the blacklist category, the emails in the far right column will turn red.

[Tony Valko]

One way...

Data Range

	A	B
1	I use Hotmail	TRUE
2	Don't use Gmail	TRUE
3	Yahoo sucks	TRUE
4	What's this?	FALSE

This formula entered in B1 and copied down:

=OR(ISNUMBER(SEARCH({"Hotmail","Gmail","Yahoo"},A1)))

[Phuocam]

Or try this ...

=MAX(COUNTIF(A1,{"*Hotmail*","*Gmail*","*Yahoo*"}))>0

[Marceti]

The first attached formula seems to be the closest to the answer that Iīm looking for.

In the "email" column, I pasted 30 different emails, some which contained "hotmail" and "yahoo", and after writing "hotmail" and "yahoo" in the "blacklist" column the emails containing theses values did not turn red.

Perhaps a formula that "contains" instead of is "equal to" the words that weīre looking for would solve the issue.

Thanks again.

[Tony Valko]

Deleted content

[Tony Valko]

Try this...

Assuming the "blacklist" is in the range A9:A13 and does not contain any empty cells.

Then, use this formula in conditional formatting:

=OR(ISNUMBER(SEARCH(A$9:A$13,D2)))

[TheMechEngineer]

In the blacklist box you only need to enter the site of the email address (yahoo,gmail,etc.) I updated the sheet so it will now find those names within the persons email address and highlight those cells red. Take a look.

[Marceti]

Hello and thank you for your support. This did it for me.

The results however do not select (we would need to delete all selected) but rather highlight. What I did here was created a personalized filter where all highlighted cells are brought up, and then proceeded to select and delete them.

If there is a quicker way to select all highlighted results let me know. Otherwise we really appreciate your help.

Thank you!

[TheMechEngineer]

I have an idea that might work, when I get a spare minute I will upload a version 3 of my sheet with that capability. If my theory ends up not working I'll also let you know.

[Marceti]

OK thank you!

[TheMechEngineer]

OK thank you!

Bad news it didn't work. I was able to get my concept to work on a test sheet (Test Find_V2), but when I transferred the macros over to the Sample email sheet it did not work.

I'll attach both documents, maybe someone can point out what I did wrong.

[Marceti]

OK donīt worry we have tried your second formula, and with the personalized filter to bring up highlighted cells and deleting them, it works out perfectly for us.

Thank you for your support!!!

[Marceti]

Hello again.

I have a question regarding your formula. Is there a way to "lock" the rules in the conditional format?
Let me explain: When we cut and paste emails to (or from) D column; or if we eliminate duplicates from D column, the values in the rules of the conditional format change; so we need to constantly open the conditional format to check if the rules are ok.

Hopefuly the explanation was understood.

Thank you for your help!