formula to check if cel contains text from column

[Donckmeister]

Hi,

I'm looking for a formula to check if a cell contain text from a colomn.
Can't seem to find it in excel 2013. Any help would be greatly appreciated! Example:

	A	B	C
1	chicago broken pipes	chicago	true
2	plumber	new york	false
3	plumbers new york	dallas	true
4	remodeling dallas	houston	true
5	frozen pipes	san jose	false
6	plumbing services houston	jacksonville	true
7	plumbing services	columbus	false
8	frozen pipes denver	detroit	false
		seattle

<tbody>
</tbody>

Many thanks!

Regards,

Donckmeister

 

[Special-K99]

Just text in general or a specific word to search for?

 

[shobolos]

=isnumber(find(b2,a2))

 

[Canapone]

Hi All,

a possible approach: in B1:B8 substrings to be recognized in A1


=ISNUMBER(MATCH(1,INDEX(SEARCH("*"&$B$1:$B$8&"*",A1),),0))

Hope it helps

 

[shobolos]

=isnumber(aggregate(15,6,find(b2,$a$2:$a$9),1))

 

[Donckmeister]

Hey guys,

Gonna test a bit more but it seems to work.

Thanks for the fast help!

 

[Donckmeister]

Hi guys,

I'm using the formula =ISNUMBER(MATCH(1,INDEX(SEARCH("*"&$B$1:$B$8&"*",A1),),0)) for some while now.
It does generate false positives. For example: "model" in colomn B would match "remodeling dallas". I only want to exactly match to the specific words not if they are part of a word.

Any ideas?

Many thanks!

 

[AliGW]

Does this make any difference?

=ISNUMBER(MATCH(1,INDEX(SEARCH("* "&$B$1:$B$8&" *",A1),),0)) - notice the spaces - untested.

 

[Donckmeister]

Hi Ali,

Thanks for replying.

It does if the word is in the middle of a sentence.
If it's at the beginning or end then it doesn't match correctly.

 

[AliGW]

Ah, yes - sorry about that.