Returning a value based on a query

[Gail]

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?

 

[Mike H]

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

 

[Gail]

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

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

 

[Gail]

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

 

[Mike H]

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

 

[T. Valko]

Try this:

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

 

[linda]

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.

 

[T. Valko]

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,"")