How do I look to see if another cell contains something

Posted by andrew miller on January 04, 2002 6:46 AM

I'm writing "if" statements. I know how to do "if a cell = something,then enter a certain value", but is there any way to say "if a cell contains a certain word, then place a certain value. For instance a column may have either "exterior wall" or "interior wall" entered, but all I'm interested in is the word "wall". Is there any way to just look to see if a cell contains the word "wall", instead of = "wall". Thanks.

Posted by Scott on January 04, 2002 7:15 AM

As long as Wall is the last word in the cell, you could use this:

=IF(RIGHT(D5,4)="WALL","1st Argument","2nd Arugument")

Change 1st and 2nd argument as needed.

Posted by Tom Urtis on January 04, 2002 7:45 AM

One option you may consider

In case the word "wall" is the first, last, or middle word in a cell within your range of interest, this formula may provide you some flexibility. Let's say your range of interest is A1:A10, and you type your search word (in your example, "wall") into B1. Then in C1 you can enter
=IF(SUMPRODUCT(ISNUMBER(SEARCH(B1,A1:A10))+0)>=1,"Yes","No")

Note, if the word "drywall" or "wallet" or "swallow" is in A1:A10, then this formula will also return a Yes -- which you might want in case there are less than perfect spellers entering data on your sheet. Just an option to consider.

Tom Urtis

Posted by Aladin Akyurek on January 04, 2002 7:50 AM

Andrew --

Lets A1:A3 house the following sample

{"exterior wall";
"interor wall";
"wallaart"}

and "wall" is the string of interest:

If you accept "wall" in "wallaart" as a positive match or a hit, then use:

=IF(ISNUMBER(SEARCH("wall",A1)),5,7)

If not, use:

=IF(SUMPRODUCT(ISNUMBER(SEARCH({" wall ","@wall "," wall@"},"@"&A1&"@"))+0),5,7)

Replace 5 and 7 with things appropriate to your situation.

Aladin

===========

Posted by David on January 04, 2002 7:51 AM

If "Wall" may be anywhere in the cell try this:

=IF(COUNTIF(D1:D1000,"*"&"Wall"&"*"),"1st Value","2nd value")

Posted by vd on January 04, 2002 12:14 PM