Find Text in cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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
 
Thanks this works great

Elkar said:
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
Click to expand...
 
Yes it works fine. Thank you

Ron Coderre said:
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
Click to expand...
 
Back
Top