Search for multiple words in a cell

[Stuck1]

Hi all,

Hope you can help. I'm trying to write a formula that will flag when a cell has a certain word in it. However, I want to search for various words. So does this this sentence in A1: "Search for multiple words in a cell"

contain the words "search", "find", "hunt" or "discover" in it?

I've tried with nesting OR's in SEARCH, MATCH and FIND, but I keep getting errors.

Thanks for any help.

 

[pedie]

will this serve your purpose?
try
'=IF(SUM(COUNTIF(B2,"*search*"),COUNTIF(B2,"*find*"),COUNTIF(B2,"*hunt*"),COUNTIF(B2,"*discover*")),"Yes","No")

 

[Stuck1]

Many thanks for the prompt and excellent response, but is there a way I can point the formula at a cell or a range rather than having the target words coded within the syntax?

I've tried :IF(SUM(COUNTIF(A6,C1),COUNTIF(A6,C2),COUNTIF(A6,C3),COUNTIF(A6,C4)),"No","Yes")
but it doesb't work.

 

[pedie]

try;

'=IF(SUM(COUNTIF(A6,"*"&C1&"*"),COUNTIF(A6,"*"&C2&"*"),COUNTIF(A6,"*"&C3&"*"),COUNTIF(A6,"*"&C4&"*")),"No","Yes")

 

[Stuck1]

Sorry, that doesn't seem to work, it returns "No", even when the target words are in those cells.

 

[pedie]

please try again like this
'=IF(SUM(COUNTIF(A6,"*"&C1&"*"),COUNTIF(A6,"*"&C2&"*"),COUNTIF(A6,"*"&C3&"*"),COUNTIF(A6,"*"&C4&"*"))>0,"Yes","No")

 

[Stuck1]

Excellent. Many thanks. It works.

 

[pedie]

You're welcome.

 

[Biz]

Another variant.

Create Named Range called MultiSearch



={"search","find","hunt","discover"}

Sheet1

	A	B	C
tyru		No
search		Yes
hunt		Yes
discover		Yes
You		No
find		Yes
find		Yes
find		Yes

<colgroup>
<colgroup><col bold;?="" 30px;="">
<colgroup><col style="width: 56px;">
<colgroup><col style="width: 56px;">
<colgroup><col style="width: 56px;"></colgroup>
<tbody>


[TD="bgcolor: #CACACA, align: center"]6[/TD]



[TD="bgcolor: #CACACA, align: center"]7[/TD]



[TD="bgcolor: #CACACA, align: center"]8[/TD]



[TD="bgcolor: #CACACA, align: center"]9[/TD]



[TD="bgcolor: #CACACA, align: center"]10[/TD]



[TD="bgcolor: #CACACA, align: center"]11[/TD]



[TD="bgcolor: #CACACA, align: center"]12[/TD]



[TD="bgcolor: #CACACA, align: center"]13[/TD]


</tbody>

Spreadsheet Formulas

Cell Formula C6 =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MultiSearch,A6))))>0,"Yes","No") C7 =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MultiSearch,A7))))>0,"Yes","No") C8 =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MultiSearch,A8))))>0,"Yes","No") C9 =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MultiSearch,A9))))>0,"Yes","No") C10 =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MultiSearch,A10))))>0,"Yes","No") C11 =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MultiSearch,A11))))>0,"Yes","No") C12 =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MultiSearch,A12))))>0,"Yes","No") C13 =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MultiSearch,A13))))>0,"Yes","No") <tbody> </tbody>

<tbody>

</tbody>

Biz

 

[T. Valko]

Hi all,

Hope you can help. I'm trying to write a formula that will flag when a cell has a certain word in it. However, I want to search for various words. So does this this sentence in A1: "Search for multiple words in a cell"

contain the words "search", "find", "hunt" or "discover" in it?

I've tried with nesting OR's in SEARCH, MATCH and FIND, but I keep getting errors.

Thanks for any help.

Here's another one.

A2 = your sentence

C2:C5 = keywords

This array formula**:

=IF(SUM(COUNTIF(A2,"*"&C2:C5&"*")),"Yes","No")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.