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...

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"gpurdue" wrote:

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...

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"gpurdue" wrote in message
...
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...

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+30 7,SEARCH("
"&$G$2:$G$6&" "," "&A2&" "))),1,""),"")

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions


In article ,
gpurdue wrote:

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...