help search text string based upon list of keywords

futurejock · Jun 29, 2010

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

ffice

ffice" /><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

Aladin Akyurek · Jun 29, 2010

Sheet1, A1:E1

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>now</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>dog</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>cow</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>crow</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>corn</TD></TR></TBODY></TABLE>

Sheet2

<TABLE style="WIDTH: 553pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=738><COLGROUP><COL style="WIDTH: 505pt; mso-width-source: userset; mso-width-alt: 23950" width=674><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 505pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2360531 class=xl65 height=19 width=674>The farmer plowed the snow with his dog</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>dog</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>The farmer plowed the snow with his doG and his crow but the cat stayed in the corn for now</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>corn</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>The farmer plowed the snow with his John Deere</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=middle>#N/A</TD></TR></TBODY></TABLE>

C1, copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Sheet1!$A$1:$E$1," "&B1),Sheet1!$A$1:$E$1)

swinglow · Jun 29, 2010

first define a named range for your list of key words. It's best if the row or column contains nothing other than your key words. if your key words will be in row one starting in A1, use this formula as the definition of your range:

=OFFSET($A$1,0,0,1,COUNTA($1:$1))

if your list will be in column A starting in A1, use this formula:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

That formula makes the range dynamic, so if you add words to it, they will be automatically included in the formula.

In my example, I defined gave the range the name "keywords"

case-sensitive:

Code:

=IF(SUMPRODUCT(--NOT(ISERROR(FIND(" "&keywords&" "," "&B1&" "))))
>0,"list match","no match")

Not case-sensitive:

Code:

=IF(SUMPRODUCT(--NOT(ISERROR(SEARCH(" "&keywords&" "," "&B1&" "))))
>0,"list match","no match")

futurejock · Jun 29, 2010

HI,

Thanks. Thanks for the quick reply, too. I can use this as is and write a separate formula but can you make this formula give:

for returns of #N/A, can it report "NO"
for returns of any keywords, can it report "YES"
?

Dan

futurejock · Jun 29, 2010

Thanks Swinglow.

I can't try your solution right away but first glance it looks nice. The issues case sense, dynamic list, and ability to label return handled.

You have been very helpful.

Dan

swinglow · Jun 29, 2010

no problem. Also, you can use Aladin's formula in conjuction with the dynamic list that I suggested. In Aladin's formula, replace "SEARCH" with "FIND" to make it case-sensitive.

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords," "&B1),KeyWords)),"NO MATCH","MATCH")

swinglow · Jun 29, 2010

if "cow" is on the list, do you want "the man had a cowface" to be a match, or not a match?

if you want that to be not a match:

=IF(ISERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&B1&" "),KeyWords)),"NO MATCH","MATCH")

or you can still use the formulas I posted earlier

Aladin Akyurek · Jun 29, 2010

futurejock said:
HI,

Thanks. Thanks for the quick reply, too. I can use this as is and write a separate formula but can you make this formula give:

for returns of #N/A, can it report "NO"
for returns of any keywords, can it report "YES"
?

Dan

Try to apply an IsNumber test on the Search result...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&Sheet1!$A$1:$E$1," "&B1))),"Yes","No")

futurejock · Jun 30, 2010

hi swinglow, hi alladin,

Thanks so much for your help.

SL- I am using 2007 btw, but may need to share with 2003 user so will try the messy.
SL - yes that's a good point, no I would not like cowface, found from cow, or otherwise.

AL- thanks yes I'll try isnumber for y/n (will isnumber disregard case sensitive?)

OK SO HERE'S a NEW thing. Can I have an exclude list within this formula? Can I search for any instance of keywords returns "yes" and a search for any instance of a separate list of keywords switches yes to "no"?

I want all cows but, no jerseys:
"the cow is a jersey" = no
"the cow is a actually a bull"

D

futurejock · Jun 30, 2010

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

help search text string based upon list of keywords

futurejock

New Member

Aladin Akyurek

MrExcel MVP

swinglow

Active Member

futurejock

New Member

futurejock

New Member

swinglow

Active Member

swinglow

Active Member

Aladin Akyurek

MrExcel MVP

futurejock

New Member

futurejock

New Member

Similar threads

Share this page

help search text string based upon list of keywords

New Member

MrExcel MVP

Active Member

New Member

New Member

Active Member

Active Member

MrExcel MVP

New Member

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock