How To Check If RANGE OF TEXTSTRINGS Contains ANY of the SEVERAL TEXT KEYWORDS In Excel?

[mtahreemalam]

Hello,
I have a range of TEXT STRINGS in column A, and a list of WORDS in column B. I want to highlight all cells which in column A that contain or match ANY WORD from the text string in column A.

 

[jeremypyle]

Go to cell a1. Then click Home, then Conditional Formatting and then New Rule and then "use formula to determine..." and then enter the following formula:

=ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(B$1:$B$3,A1),$B$1:$B$3))=FALSE

Then choose to highlight with the colour you want and click apply

please replace $b$3 with the number of the last cell in column B. This only works up until the last cell. If you want to select the entire column B, you would need to just enter random text in the rest of column B. For example, you could enter "RANDOM" into every cell in column B after your input to fill it up

Then you could enter
=ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(B:B,A1),B:B))=FALSE

Let me know how this works

 

[DanteAmor]

Try this

Select the cells in the column A and put the following formula in the conditional format.

=SUM(IF($B$1:$B$400<>"",--ISNUMBER(SEARCH($B$1:$B$400,$A1))))

 

[jeremypyle]

Or this formula in cell a1 and copy the formatting to all the other cells in column A

=ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(INDIRECT("B1:B"&LOOKUP(2,1/(B:B<>""),ROW(B:B))),A1),INDIRECT("B1:B"&LOOKUP(2,1/(B:B<>""),ROW(B:B)))))=FALSE

 

[jeremypyle]

DanteAmor formula is shorter though!

 

[mtahreemalam]

hey i tried both. only DanteAmor formula did the highlighting part but it was of the incorrect cells.

 

[mtahreemalam]

test sheet.xlsx

drive.google.com

 

[mtahreemalam]

Here is a link of the file for you to work with, if could find it easier

 

[DanteAmor]

hey i tried both. only DanteAmor formula did the highlighting part but it was of the incorrect cells.

The formula works well for me, I return the file with the formula.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Peter_SSs]

match ANY WORD from the text string in column A.

Since you are trying to match "WORDS" I think the previous suggestions will fail since they will match any substring rather than whole words. For example, I note that at least one of the suggestions highlights "headrest pillow" when column B contains neither "headrest" nor "pillow" (but does contain "pill", hence the substring match)

Try this instead. (Partial sheet only shown)

Book1
	A	B
1	TEXT STRINGS	Keywords
2	headrest tablet mount	Battery
3	headrest hooks	Alexa
4	headrest dvd player	xbox
5	headrest ipad holder	Décor
6	headrest tablet holder	pill
7	headrest pillow	tv
8	headrest covers for cars	jacket
9	same day	lotion
10	same day delivery	Rechargable
11	same pennis forever banner	playstation
12	peptides serum for face	decoration
13	peptides colagen	Thermometer
14	peptides for face	shirts
15	peptides powder	perfume
16	peptides serum	batteries
17	peptides cream	nintendo
18	peptides cream for face	gift
19	windbreaker men	mg
20	windbreaker women	hoodie
21	windbreaker pants men	deodorant
22	windbreaker jacket women	oil
23	windbreaker pants women	yoga mat
24	windbreaker jacket	soap
25	windbreaker pants	iphone
26	windbreaker mens jacket	lubricant
27	windbreaker women 80s	book
28	howard stern comes again	solution
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A228	Expression	=LOOKUP(9E+99+307,SEARCH(" "&$B$2:$B$200&" "," "&A2&" "))	text	NO

but it was of the incorrect cells.

BTW, when you report something like that, it gives helpers nothing to go on. Give some examples of what was highlighted when it shouldn't be and explain why or what was not highlighted and should have been and why. You need to help us help you.