help search text string based upon list of keywords

[futurejock]

Hi I need a formula that will use a column list of words as "find text," and the formula will tell me if any instance of any keyword in that list occurs anywhere in a single cell text string. Any word only needs to occur, it doesn't matter how many or which or where.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
The formula also needs to tell me “no” or indicate even with a blank there was no instance.<o></o>
<o></o>
It needs to be not case sensitive. I also want exact matches only.<o></o>
<o></o>
For instance if:<o></o>
<o></o>
Referencing a list sheet1!A1:E1<o></o>
now<o></o>
dog<o></o>
cow<o></o>
crow<o></o>
corn<o></o>
<o></o>
I have a column of sentences in column B, <o></o>
but want to check each cell individually<o></o>
and sentence in B1 cell is:<o></o>
"The farmer plowed the snow with his dog"<o></o>
<o></o>
I'd like C1 to say: "yes". Because the keyword "dog" is in the cell's text string. (or actually it would be great if it could return a word of my choosing. But y/n, true/false will do.)<o></o>
<o></o>
if the sentence says:<o></o>
"The farmer plowed the snow with his doG"<o></o>
I want the same answer: yes<o></o>
<o> </o>
if the sentence says:<o></o>
"The farmer plowed the snow with his doG and his crow but the cat stayed in the corn for now"<o></o>
I want the same answer: yes<o></o>
<o> </o>
<o></o>
if the sentence says:<o></o>
"The farmer plowed the snow with his John Deere"<o></o>
No keywords were used. I need to make sure the formula doesn't return "yes" on the instance that the keyword "now" (from the list) is part of the word "snow"<o></o>
<o> </o>
Also suppose the keyword list did not include the word "now," but instead said “ow,” I'd need the return to NOT say yes on the instance that the “ow” can be found in “snow” and “plowed.” Also in the absence of “ow” from the list, I’d need “cow” and crow” NOT to return the "ow" combination found in the sentence.<o></o>
<o></o>
It would be a bonus if I could add words to this list and have the formula include automatically the new words, and data automatically update.<o></o>
<o></o>
It would be a bonus I could manually adjust the formula to include additional searches for single keywords from other columnrows of text data. As in if it could do the above, plus add if the word duck appears in text from columnrow Z1, (same row as the cell in question), and even if no key words are in the cell string, also say "yes."<o></o>
<o></o>
Thanks,<o></o>
<o></o>
Dan

 

[swinglow]

if the exclusion list takes precedance over the inclusion list, then you can use the same formula, just nested in an IF statement:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,
   SEARCH(" "&ExclusionWords&" "," "&D1&" "))),"No",
    IF(ISNUMBER(LOOKUP(9.99999999999999E+307,
     SEARCH(" "&KeyWords&" "," "&D1&" "))),"Yes","No"))

SEARCH is Not case-sensitive
FIND IS case-sensitive

 

[Aladin Akyurek]

and also would be useful; or even if the previous can't be done:

I would use a formula that requires inclusion of one unique keyword, and then returns yes, but returns no if any instance from a dynamic list occurs.

Dan

Assuming that you define IncludeList and ExcludeList in an approprate manner, define also BigNum as referring to:

=9.99999999999999E+307

Invoke:

=LOOKUP(BigNum,CHOOSE({1,2,3},1,
   LOOKUP(BigNum,SEARCH(" "&IncludeList&" "," "&B1&" ")),
   LOOKUP(BigNum,SEARCH(" "&ExcludeList&" "," "&B1&" "))),
    {"no","yes","no"})

 

[futurejock]

HI SL,

I seem to have lost the thread for "messy" workaround code I need for excel 2003.

do you have / is it still in the thread (do you think?)

It's weird I thought I'd used Aladins formula with your suggestions and it had done well, and cannot get it to work now, so I guess I was in 2007 version?

Anyway sorry I am kind of lost.

D

 

[futurejock]

HI Alladin, and SwingL,

Sorry about and never mind the whining: my last message.

I figured it out. Another variable I'd changed was in the way.

So now feeling good.

Off to test the nested.

D