Conditional Formatting (specific text That contains)

[Mario Rivera]

I have learned to use conditional formatting for one particualr item and use a color for it. Here is my issue. I deal with many emails and contantly have to check the list of emails employee list on their profile. I have learned to spot the errors quickly (visually). How can I use conditional formatting to highlight the correct emails under one condition rather than having to create 5 conditions for each of the right email address. For example I want to highlight the emails that have the right spelling such as msn.com, @aol.com, @hotmail.com, @gmail.com and even @comcast.net. I am able to set a conditional format for each one. So I am asking if there is a way to format one condition for all of them at once?? This way those that are not highlighted based on the condition have a spelling error on the email address (i.e. @gamil.com or @comcast.com) or have the wrong punctuation (i.e. @gmail,com)

mario

[#2]

Hello,

a way would be to add a helper column and check if the domain name matches a predefined list, you can also extend. Example:

=IFERROR(IF(MATCH(RIGHT(A2,LEN(A2)-FIND("@",A2)),$D$2:$D$6,0)>0,1,0),0)

If the domain name was found, the result is 1, else 0. The list is in $D$2:$D$6. Then, apply a conditional format by using the helper column.

I attached an example file. Hope, this helps.

Best,

Mourad

[#3]

Mr. Mourad,

Thank you for the quick reply. what you sent me makes sense, did not realize that the IF could be taken that far. I guess I still have much to learn in excel. I will then now take your formula and break it down so that I can understand every part of it. I did noticed the match formula in it which I am very familiar with it, but the rest of the formula is greek to me. Again thank you so much for the help, now its time to learn from what you sent me.

Sincerely,

Mario