find text in a string formula

T

Todd

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
 
T

Todd

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

Todd
 
M

Max

Todd said:
.. Sometimes the word is not first though, it might be anywhere.
Click to expand...

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)
 
H

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")
Click to expand...

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?
 
Top