ISTEXT or SEARCH function with 2 variables

**mobycane** · 07-08-2010, 04:20 AM

Hi all,

Assuming that I have some words in column A and I specifically want to retrieve those rows with the word "Apple" or "Orange" which do not exist in the same row:

Item (column A) Extracted column (column B)
Apple is a good fruit Apple is a good fruit
mango
banana
HOT turkey
I love oranges I love oranges
Red Apples Red Apples
Red Oranges Red Oranges

What is the formula that I should use in order to get the answers in column B ?

Appreciate all your kind reply!!

**Marcol** · 07-08-2010, 04:31 AM

One way might be

=IF(ISERROR(FIND("pple",A1,1)),IF(ISERROR(FIND("range",A1,1)),"",A1),A1)

Hope this helps

**mobycane** · 07-08-2010, 04:38 AM

Originally Posted by Marcol

One way might be

=IF(ISERROR(FIND("pple",A1,1)),IF(ISERROR(FIND("range",A1,1)),"",A1),A1)

Hope this helps

Thanks for the lightning quick solution!

Quick question, can I use the 'SEARCH' function instead since I thought it is not case sensitive?

**sweep** · 07-08-2010, 04:31 AM

Hi,

=IF(OR(ISNUMBER(SEARCH("Apple",A1)),ISNUMBER(SEARCH("orange",A1))),A1,"")

replace search with find to make the formula case dependant.

**DonkeyOte** · 07-08-2010, 04:38 AM

Another variation

=IF(COUNT(SEARCH({"Apple","Orange"},A1)),A1,"")

Adjust the inline array for more / less terms of interest

**Marcol** · 07-08-2010, 04:49 AM

mobycane

Go with sweeps solution, it's much better than mine!

[EDIT]
So is DonkeyOtes, didn't see it before posting reply.

That's the problem with me, "lightning quick solution", and not really thinking!