# formula to check if cel contains text from column

#### Donckmeister

##### New Member
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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Just text in general or a specific word to search for?

=isnumber(find(b2,a2))

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

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

Hey guys,

Gonna test a bit more but it seems to work.

Thanks for the fast help!

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!

Does this make any difference?

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

Hi Ali,

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.

Ah, yes - sorry about that.

Replies
4
Views
210
Replies
6
Views
774
Replies
3
Views
526
Replies
7
Views
8K
Replies
2
Views
388

1,203,116
Messages
6,053,603
Members
444,673
Latest member
DWriter9

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back