Find Text in cells

[Guest]

Hi
I need a formula that will confirm if certain text that is in one cell is
contained in another cell ie

Cell A1
Oranges Apples Bananas

Cell B1
Apples

Cell C1
I need a formula to return that B1 was found in A1.

The formula I'm using is
=IF(FIND(B1,A1)<>"","yes","")
This works fine when the word in B1 is contained in A1 but I'm getting
#VALUE when the word is not in A1
Can anyone help?

 

[Guest]

Try this:

=IF(ISERROR(FIND(B1,A1)),"","yes")

Note that FIND is case sensitive, so "Apples" would be found, but "apples"
would not. Replacing FIND with SEARCH would ignore case.

HTH,
Elkar

 

[Guest]

With

A1: Oranges Apples Bananas
and
B1: apples

This formula test if A1 contains the text from cell B1:
C1: =IF(COUNTIF(A1,"*"&B1&"*"),"Yes","")

Note: COUNTIF is NOT case-sensitive
Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks this works great

Try this:

=IF(ISERROR(FIND(B1,A1)),"","yes")

Note that FIND is case sensitive, so "Apples" would be found, but "apples"
would not. Replacing FIND with SEARCH would ignore case.

HTH,
Elkar

 

[Guest]

Yes it works fine. Thank you

With

A1: Oranges Apples Bananas
and
B1: apples

This formula test if A1 contains the text from cell B1:
C1: =IF(COUNTIF(A1,"*"&B1&"*"),"Yes","")

Note: COUNTIF is NOT case-sensitive
Does that help?
***********
Regards,
Ron

XL2002, WinXP