Return a value if criteria contained in cell

[Guest]

I would like to return a value if criteria is met.

If A2 contains the word "apples", then in cell B2, write "apples".
If A2 contains the word "oranges, then in cell B2, write "oranges".
If A2 does not contain the words "apples" or "oranges", leave the cell blank.

Can you help me with a formula for this?

Thanks in advance.

 

[Guest]

IF(OR(A2="apples",A2="oranges"),A2,"")

 

[Biff]

Hi!

Try one of these:

=IF(OR(A2={"apples","oranges"}),A2,"")

Biff

 

[Guest]

In cell A2, it could be "apples and pears" or it could be "oranges and
pears". What I want to do is in cell B2, just return either "apples" or
"oranges", disregarding the "and pears".

How would I do that?

Thanks for the quick response.

 

[Biff]

Well, that's a whole different animal!

Try this:

=IF(ISNUMBER(SEARCH("apples",A2)),"apples",IF(ISNUMBER(SEARCH("oranges",A2)),"oranges",""))

What if it's "apples or oranges" or "oranges or apples" ?

You'd be better off using a cell to hold the criteria:

A1 = apples
B1 = oranges

=IF(ISNUMBER(SEARCH(A1,A2)),A1,IF(ISNUMBER(SEARCH(B1,A2)),B1,""))

Biff

 

[Guest]

This worked perfectly. Now another question -- do you know how I would put
this in an Access query?

I am using Excel to run the query, but after thinking, would rather do it
right in Access and let it come over from the query. Or should I post this
question on the Access group?

 

[Biff]

I know nothing about Access. You should post that question in an Access
forum but you could also keep an eye on this thread in case someone chimes
in with a solution.

Biff

 

[Guest]

If you are using Access, I would assume you are more using a table.

Follow SQL could do.
SELECT
CASE
WHEN x like '%apples%" THEN "apples"
WHEN x like '%oranges%" THEN "orange"
ELSE
""
FROM
table list