Formula to find certain word in cell

[jw01]

Hi there

I have data in range A2:A1000. It contains sentences.

What I'm trying to figure out is a way to let me know, if certain cells contain specific word "immigration" or "immi" or "immig" etc

Here is an example
AGRWL, Sid (XVW) Immigration Con

SO basically, if the formula can let me know with a "True" or "false" if it contains immigration or a rendition of that word. pls and thx you!

I tried this formula, but it's not working properly

Please Login or Register  to view this content.

pls help..and thx you!

[Pete_UK]

All you need is this:

=ISNUMBER(SEARCH("immigration",A20))

It will return either TRUE or FALSE.

You could also make use of COUNTIF and wildcards, like this:

=COUNTIF(A20,"*immigration*")>=1

Hope this helps.

Pete

[jw01]

hey guys


just one more modification....i noticed i need additional words that I need to check along with "immigration"...can you guys let me know how I can add more criteria?

For example, I need the formula to check for the following types of word, and give me a 1 or yes
immigration
immirg
immigrati
H-1B
H-4


there may be other words as well, but those are it for now....thxs all!

[63falcondude]

Here's one way:

=SUM(COUNTIF(A20,{"*immig*","*H-1B*","*H-4*"}))