How search for number of text strings in cell

somanyqs

Board Regular
Joined
Oct 2, 2007
Messages
76
I can't seem to get the "OR" working:

What I would like to do is have
A1:A100 has a list of different animals
B1:B100 return a specific text string based on what it finds in A:A
C1:C100 are the quantities of the animal in B:B on the farm
D1:D?? are the text strings to search for in A:A, which will keep increasing over time.

Example:
A1= Furry brown sharp pointy toothed boar

D1 = dog
D2 = cat
D3 = bird

What I would like is for B1 to look at A1, see if any of D:D appear, if so then it returns "This is a safe pet!". If not, then it just returns A1. And so forth down the rows...

So in the case above
B1 = Furry brown sharp pointy toothed boar
Now if someone decides to add boars to the list of safe animals, so inputs a string in a new cell
D4 = boar

Then
B1 = This is a safe pet!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
What I was first trying to use, but I think I did something incorrect with the "OR" (and then from here I was going to make the next step to try to remove "dog", "cat", etc. and switch to D:D

B1=IF(ISERROR(OR((FIND("dog", A1)),(FIND("cat",A1)))),A1,"This is a safe pet!")

Had it working for just one find:
B1=IF(ISERROR(FIND("cat",A1)),A1,"This is a safe pet!")
 
Last edited:
Upvote 0
Getting warmer...working for D1 and D2, but now need to figure out how to expand so people can adding safe pets in column D over time.

B1 = IF(OR(NOT(ISERROR(FIND(D$1,A1))),NOT(ISERROR(FIND(D$2,A1)))),"safe pet",A1)

I think I'm not understanding the FIND.
Like I would think that
B1 = IF(FIND(D:D,A1),"safe pet",A1)
should work...
 
Upvote 0

Forum statistics

Threads
1,224,574
Messages
6,179,626
Members
452,933
Latest member
patv

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top