find text in a string formula
 

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

find text in a string formula
 

left(aa7,5)<"secur"

--
Don Guillett
SalesAid Software

"Todd" wrote in message
...
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

find text in a string formula
 

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

Todd

"Don Guillett" wrote:

left(aa7,5)<"secur"

--
Don Guillett
SalesAid Software

"Todd" wrote in message
...
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

find text in a string formula
 

"Todd" wrote:
.. Sometimes the word is not first though, it might be anywhere.
... = IF(AA7<"secur", ..

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

find text in a string formula
 

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.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

find text in a string formula
 

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?