text exist

[gpurdue]

I have misplaced a formula that I had in the past to accomplish the
following: A cell contains a string of text and within that text are certain
keywords that I need to determine if exists within a table/array of critical
keywords. For example if I have a text field that contains the phrase
"property taxes are due Jan 1" and in have a keyword table/array that
contains the word "taxes" as one of the entries I would like to flag that
particular entry. I believe I had accomplished this with the use of the
search formula in the past but I have been unable to reproduce. Any
assistance would be appreciated as I have exhaused my knowledge...

 

[Luke M]

Range of keywords in range A2:A10
Cell to search is B2

=SUMPRODUCT(ISNUMBER(SEARCH($A$2:$A$10,B2))*1)

will tell you how many key words are found within cell.

 

[Bernard Liengme]

In F1:F4 I have this as a list of 'keywords"
taxes
bribes
death
justice

In A1:A3 I have these test phases
property taxes are due Jan 1
hello
death and taxes

In B1 I have this formula
=SUMPRODUCT(--ISNUMBER(SEARCH($F$1:$F$4,A1)))
It is copied down to B3

The values in B1:B3 are; 1, 0, 2
Does this do what you want?

You could use the formula in Conditional Formatting to colour the
appropriate cells

You could use =SUMPRODUCT(--ISNUMBER(SEARCH($F$1:$F$4,A1)))>0 to get TRUE or
FALSE
best wishes

 

[Domenic]

Assuming that A2 contains the text string, and G2:G6 contains the
keyword table, try...

=IF(A2<>"",LOOKUP(9.99999999999999E+307,SEARCH(" "&$G$2:$G$6&" ","
"&A2&" "),$G$2:$G$6),"")

Or, if you prefer, try...

=IF(A2<>"",IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH("
"&$G$2:$G$6&" "," "&A2&" "))),1,""),"")