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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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(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

"jhicsupt" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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(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
>
> "jhicsupt" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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" <[email protected]> wrote in message
news:[email protected]...
> 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(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
>>
>> "jhicsupt" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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(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
> >
> > "jhicsupt" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >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.
> > >>
> > >>
> > >>
> >
> >
> >