find text in a string formula

[Guest]

Here is the formula I am using. The numeric if's work but the text one does
not. Cell AA7 contains a text statement and in that statement the term secure
or security might appear. If if does I want to return an "X". If it does
not I want to continue to evaluate the other cells.

IF(AA7<>"secur",IF(AND(E7<>"",F7<>"",G7<>"",I7<>"",P7<>""),IF(AND(Q7<>"",S7<>"",V7<>"",Q7<>"",AB7<>""),"P","X"),"X"),"P")

Thanks

Todd

 

[Don Guillett]

left(aa7,5)<>"secur"

 

[Guest]

Thanks Don, that works. Sometimes the word is not first though, it might be
anywhere. Thats what has me stumped

Todd

 

[Guest]

.. Sometimes the word is not first though, it might be anywhere.

Try this as a replacement for the front IF part above:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"secure";"security"},AA7)))=0, ..

Replace SEARCH with FIND if you need it to be case sensitive
(SEARCH is not case sensitive)

---

 

[Guest]

Just a clarification that the suggested formula was based on your OP lines:

.. Cell AA7 contains a text statement
and in that statement the term secure or security might appear.

---

 

[Harlan Grove]

Todd wrote...

Here is the formula I am using. The numeric if's work but the text one does
not. Cell AA7 contains a text statement and in that statement the term secure
or security might appear. If if does I want to return an "X". If it does
not I want to continue to evaluate the other cells.

IF(AA7<>"secur",IF(AND(E7<>"",F7<>"",G7<>"",I7<>"",P7<>""),
IF(AND(Q7<>"",S7<>"",V7<>"",Q7<>"",AB7<>""),"P","X"),"X"),"P")

It looks like you have a typo in this formula - there's no need for
Q7<>"" to appear twice.

Could AA7 contain words like 'unsecured' or 'insecurity'? If so, should
these be treated the same as finding 'secure' or 'security'? If so, use
Max's formula. If not, would these words appear delimited by spaces?