IF question

[castruck]

when doing an IF question and I want to have the Logic Test = a single
word in the cells and have it trigger a true or false..

AC21 says "Office/Retail" i want my formula to only read the
"Office" part of it

=IF(AC21="Office","Yes","No")

I want it to still come up as "Yes" in the new cell but it wont
because cell AC21 also says "/Retail"

so the question is.. how do i get my formula to read just the "Office"
part of cell AC21??

something on the lines of "If AC21 has the word "Office" anywhere in
it then the new cell will = "Yes""

 

[PCLIVE]

One way:

=IF(ISERROR(SEARCH("Office",AC21)),"No","Yes")

HTH,
Paul

 

[Dave O]

By nesting a SEARCH function inside the IF, you can do what you need:
=IF(SEARCH("office",AC21,1)>0,"Yes","No")

SEARCH looks for the string "office" in cell A21 and returns its
position in the string if it is there, or zero if it is not. SEARCH is
similar to FIND, but SEARCH is not case sensitive, where FIND looks
for an exact case match.

Have a good weekend!
Dave O

 

[Dave O]

My error: Paul's kung fu is better. I thought a negative search would
result in zero, as opposed to an error.

 

[castruck]

Thanks guys!! That worked out great =)