Text fomula

[gregork]

In cell B1 I could have any one of the following text examples: big apple,
small apple, orange, apple, pear etc. In A1 I want a formula that will
return "apple" if B1 contains any of the text apple. ie if it read "big
apple" in B1 I want it to return "apple" in A1.

Thanks
GK

 

[Dave O]

Check out the FIND() and SEARCH() functions. Then you could write an
IF() in A1 that says 'if the result of the FIND is greater than zero,
write "apple" in A1'.

 

[JE McGimpsey]

One way:

=IF(COUNTIF(B1,"*apple*"),"apple","")

 

[Tom Ogilvy]

=if(counif(B1,"*apple*)>0,"apple","")

 

[Tom Ogilvy]

Left of a double quote:
=if(counif(B1,"*apple*")>0,"apple","")

 

[gregork]

Thanks Guys.....does exactly what I wanted.

Cheers
GK

 

[Jack Sons]

Tom,

Suppose in A1:F6 there can be all kinds of things (text, numbers, blanks,
formulae), but I want to check if "apple", "pear", "cherry"; "coconut" and
"apricot" each occur at least once. If so a formula in H1 should return
TRUE, if not FALSE.
I tried
=COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},A1:F6,0))=5
but it won't work, also when array entered.
Your help will be appreciated.

Jack Sons
The Netherlands

 

[R.VENKATARAMAN]

try type in H1

=IF(OR(COUNTIF(D516,"apple"),COUNTIF(D516,"pears"),COUNTIF(D516,"cherr
y"))>=1,TRUE,FALSE)

your data is in d5 to d16.

do you get what you want.

may be tortuous to write .may be bugs in it. try the experiment

====================================