Conditional formatting depending on different strings of text

[mbk0523]

Hi,

I have a list of email addresses in which I want to highlight ones that contain certain words from a separate list. For example, I want to highlight any email addresses where the words "info" or "hello" or "feedback" come before the "@", in situations where it's just that word (info@_____.com) and where it appears in a string (compay.info@____.com).

I also want to do the same with email addresses where certain words appear after the "@". Let's say I want to remove "@genericcompany.com" and any ".gov" and ".org" email addresses.

Thanks for the help!

 

[Asbestos_Jen]

This seems to do what you're looking for, though it's pretty clunky.

Book1
	B	C	D	E	F	G	H
2	crusader@genericcompany.com	TRUE					Keywords
3	feedback@me.com	TRUE					info
4	geeber@verizon.org	TRUE					hello
5	mike@optonline.net	FALSE					feedback
6	info@icloud.com	TRUE					genericcompany.com
7	jaarnial@gmail.gov	TRUE					.gov
8	kaiser@aol.com	FALSE					.org
9	kourai@yahoo.com	FALSE
10	mbrown@aol.com	FALSE
11	neonatus@hotmail.com	FALSE
12	research@icloud.com	FALSE
13	yzheng@aol.com	FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=OR(ISNUMBER(FIND($H$3,B2)),ISNUMBER(FIND($H$4,B2)),ISNUMBER(FIND($H$5,B2)),ISNUMBER(FIND($H$6,B2)),ISNUMBER(FIND($H$7,B2)),ISNUMBER(FIND($H$8,B2)))

 

[mbk0523]

Thanks, this is helpful. Is there no way to do it more efficiently? What if the keywords in column H is 50 items long...?

 

[Peter_SSs]

Try like this. It assumes no blank cells within the keywords list.

24 10 21.xlsm
	B	C					H
1
2	crusader@genericcompany.com						Keywords
3	feedback@me.com						info
4	geeber@verizon.org						hello
5	mike@optonline.net						feedback
6	info@icloud.com						genericcompany.com
7	jaarnial@gmail.gov						.gov
8	kaiser@aol.com						.org
9	kourai@yahoo.com
10	mbrown@aol.com
11	neonatus@hotmail.com
12	research@icloud.com
13	yzheng@aol.com
Highlight

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B13	Expression	=COUNT(SEARCH($H$3:$H$8,B2))	text	NO

 

[Peter_SSs]

Try like this.

Sorry, I had not read your requirements carefully enough. Try this one instead. Note that I have tried to include instances (eg rows 10 & 11) where a country code might be added at the end after things like .com or .gov etc. Is that what you would want?

24 10 21.xlsm
	B	C					H	I
1
2	crusader@genericcompany.com						Before	After
3	feedback@me.com						info	genericcompany.com
4	geeber@verizon.org						hello	.gov
5	mike@optonline.net						feedback	.org
6	info@icloud.com							hotmail.com
7	jaarnial@gmail.gov
8	kaiser@aol.com
9	kourai@yahoo.com
10	mbrown@abcd.gov.uk
11	neonatus@hotmail.com.au
12	research@icloud.com
13	hello.you@aol.info.com
Highlight (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B13	Expression	=OR(COUNT(SEARCH("."&H$3:H$5&"@","."&B2)),COUNT(SEARCH("@*"&I$3:I$6&".*",B2&".")))	text	NO

 

[MARZIOTULLIO]

Merch Totals.xlsx
	A	B	C	D	E	F	G
2	crusader@genericcompany.com
3	feedback@me.com						info
4	geeber@verizon.org						hello
5	mike@optonline.net						feedback
6	info@icloud.com						genericcompany.com
7	jaarnial@gmail.gov						.gov
8	kaiser@aol.com						.org
9	kourai@yahoo.com						hotmail.com
10	mbrown@abcd.gov.uk
11	neonatus@hotmail.com.au
12	research@icloud.com
13	hello.you@aol.info.com
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A100	Expression	=SUMPRODUCT(COUNTIF($A2,"*"&$G$3:$G$9&"*"))>0	text	NO

 

[Peter_SSs]

@MARZIOTULLIO
I'm not sure if (like I did initially) you have initially missed these requirements?

highlight any email addresses where the words "info" or "hello" or "feedback" come before the "@", in situations where it's just that word (info@_____.com) and where it appears in a string (compay.info@____.com).

I also want to do the same with email addresses where certain words appear after the "@". Let's say I want to remove "@genericcompany.com" and any ".gov" and ".org" email addresses.

Below I have used your formula and, by my reading of the conditions, none of the items in column A should be highlighted.

24 10 21.xlsm
	A	B	C	D	E	F	G
1
2	geeber@verizon.organisation.com
3	mike@infoline.net						info
4	zinform@icloud.com						hello
5	kaiser@aol.com						genericcompany.com
6	new.organics@yahoo.com						xxx
7	tom.govant@abc.com						.gov
8	research@icloud.com						.org
9	col.chellox@aol.com						hotmail.com
Highlight (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A9	Expression	=SUMPRODUCT(COUNTIF($A2,"*"&$G$3:$G$9&"*"))>0	text	NO

 

[MARZIOTULLIO]

Book1
	A	B	C	D	E	F	G	H	I
2	geeber@verizon.organisation.com			TRUE					org
3	mike@infoline.net			TRUE			info		info
4	zinform@icloud.com			TRUE			hello		info
5	kaiser@aol.com			FALSE			genericcompany.com
6	new.organics@yahoo.com			TRUE			xxx		org
7	tom.govant@abc.com			TRUE			.gov		gov
8	research@icloud.com			FALSE			.org
9	colchellox@aol.com			TRUE			hotmail.com		hello
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=SUMPRODUCT(COUNTIF(A2,"*"&$G$3:$G$9&"*"))>0

 

[Peter_SSs]

@MARZIOTULLIO
I'm not sure what post #8 is about? As far as I can see it simply reinforces my point.

Take row 7 as an example. Cell A7 is not a ".gov" email address even though it contains the characters ".gov" and in any case the ".gov" in that cell does not come after the "@" symbol as outlined in post #1

Take row 3 as another example. Your formula says TRUE because it contains the text "info" but the OP wanted highlighted if that text came before the "@" symbol and was by itself, or preceded by a ".', as shown in post #1

Anyway, I guess the OP will confirm one way or the other at some point.