Find Text in cells
 

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?

Find Text in cells
 

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


"jimar" wrote:

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?

Find Text in cells
 

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


"jimar" wrote:

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?

Find Text in cells
 

Thanks this works great

"Elkar" wrote:

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


"jimar" wrote:

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?

Find Text in cells
 

Yes it works fine. Thank you

"Ron Coderre" wrote:

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


"jimar" wrote:

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?