Return a value if criteria contained in cell
 

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.

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

"jhicsupt" wrote:

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.

Hi!

Try one of these:

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

Biff

"jhicsupt" wrote in message
...
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.

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" wrote:

Hi!

Try one of these:

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

Biff

"jhicsupt" wrote in message
...
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.

Well, that's a whole different animal!

Try this:

=IF(ISNUMBER(SEARCH("apples",A2)),"apples",IF(ISNU MBER(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

"jhicsupt" wrote in message
...
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" wrote:

Hi!

Try one of these:

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

Biff

"jhicsupt" wrote in message
...
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.

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" wrote:

Well, that's a whole different animal!

Try this:

=IF(ISNUMBER(SEARCH("apples",A2)),"apples",IF(ISNU MBER(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

"jhicsupt" wrote in message
...
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" wrote:

Hi!

Try one of these:

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

Biff

"jhicsupt" wrote in message
...
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.

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

"jhicsupt" wrote in message
...
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" wrote:

Well, that's a whole different animal!

Try this:

=IF(ISNUMBER(SEARCH("apples",A2)),"apples",IF(ISNU MBER(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

"jhicsupt" wrote in message
...
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" wrote:

Hi!

Try one of these:

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

Biff

"jhicsupt" wrote in message
...
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.

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


"jhicsupt" wrote:

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" wrote:

Well, that's a whole different animal!

Try this:

=IF(ISNUMBER(SEARCH("apples",A2)),"apples",IF(ISNU MBER(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

"jhicsupt" wrote in message
...
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" wrote:

Hi!

Try one of these:

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

Biff

"jhicsupt" wrote in message
...
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.