Returning a value based on a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gail - 20 Jul 2008 15:02 GMT

Hi.

What function would I use to look for specific text in a cell, i.e. Rescind
or Cancel, and return the value R or C in a designated cell?

Reply to this Message

Mike H - 20 Jul 2008 15:09 GMT

Hi,

If the cell could contain anything try this

=IF(A1="Rescind","R",IF(A1="Cancel","C",""))

If it will only contain Rescind or Cancel try this

=LEFT(A1,1)

Mike

> Hi.
>
> What function would I use to look for specific text in a cell, i.e. Rescind
> or Cancel, and return the value R or C in a designated cell?

Reply to this Message

Gail - 20 Jul 2008 15:18 GMT

Thanks. I'll try that. Have a good one!

> Hi,
>
[quoted text clipped - 12 lines]
> > What function would I use to look for specific text in a cell, i.e. Rescind
> > or Cancel, and return the value R or C in a designated cell?

Reply to this Message

Gail - 20 Jul 2008 15:32 GMT

HI.

The cell I want to do the query on contains other data. I want to find the
text "Rescind" and return the "R" value.

What is the best function to use?

Gail

> Hi,
>
[quoted text clipped - 12 lines]
> > What function would I use to look for specific text in a cell, i.e. Rescind
> > or Cancel, and return the value R or C in a designated cell?

Reply to this Message

Mike H - 20 Jul 2008 16:15 GMT

Maybe this

=IF(ISNUMBER(SEARCH("Rescind",A1)),MID(A1,SEARCH("Rescind",A1),1),IF(ISNUMBER(SEARCH("Cancel",A1)),MID(A1,SEARCH("Cancel",A1),1),""))

Mind the line wrap, its all on one line.

Mike

> HI.
>
[quoted text clipped - 21 lines]
> > > What function would I use to look for specific text in a cell, i.e. Rescind
> > > or Cancel, and return the value R or C in a designated cell?

Reply to this Message

T. Valko - 20 Jul 2008 18:19 GMT

Try this:

=IF(COUNTIF(A1,"*cancel*"),"C",IF(COUNTIF(A1,"*rescind*"),"R",""))

Signature

Biff
Microsoft Excel MVP

> HI.
>
[quoted text clipped - 23 lines]
>> > Rescind
>> > or Cancel, and return the value R or C in a designated cell?

Reply to this Message

linda - 31 Jul 2008 18:04 GMT

I also have a question related to this topic.
I need to write a forumula that identifies a cell value on a row "if <1",
then to return the value of a text cell on the same row.
e.g. if B2<1 then return the text value of cell C2 as the answer.
Probably simple but ???
Thanks.

> Try this:
>
[quoted text clipped - 27 lines]
> >> > Rescind
> >> > or Cancel, and return the value R or C in a designated cell?

Reply to this Message

T. Valko - 31 Jul 2008 18:41 GMT

Try this:

=IF(B2<1,C2,"")

Note that if B2 is an empty cell, an empty cell evaluates as 0 and 0 is less
than 1 so the result will be the value of C2. If you don't want that to
happen try this version:

=IF(AND(COUNT(B2),B2<1),C2,"")

Signature

Biff
Microsoft Excel MVP

>I also have a question related to this topic.
> I need to write a forumula that identifies a cell value on a row "if <1",
[quoted text clipped - 34 lines]
>> >> > Rescind
>> >> > or Cancel, and return the value R or C in a designated cell?

Reply to this Message