Hi i am using the following formula to find text in a cell
=IF(ISNUMBER(FIND("Super",B96)),"OK", "Not OK") and return a value. i have
adapted the formula for another spreadsheet and simply added more values to
check, i.e. =IF(ISNUMBER(FIND("Super""Account""Business:,B96)) ,"OK", "Not
OK") but it doesnt seem to work. where i can see the value in the cell it is
not returning ok instead of not ok. is there a limit to the number of values
i can search and if so why doesnt it say as an error? what am i missing?
Thanks very much

The find fuction will only let you search for a single string so you will
need to adapt your formula to something like:

=IF(OR(ISNUMBER(FIND("Super",B96)),ISNUMBER(FIND(" Account",B96)),ISNUMBER(FIND("Business",B96))),"OK ","Not OK")

(watch out for the line wrap)

Hope this helps
Rowan

"Micayla Bergen" wrote:

Hi i am using the following formula to find text in a cell
=IF(ISNUMBER(FIND("Super",B96)),"OK", "Not OK") and return a value. i have
adapted the formula for another spreadsheet and simply added more values to
check, i.e. =IF(ISNUMBER(FIND("Super""Account""Business:,B96)) ,"OK", "Not
OK") but it doesnt seem to work. where i can see the value in the cell it is
not returning ok instead of not ok. is there a limit to the number of values
i can search and if so why doesnt it say as an error? what am i missing?
Thanks very much

Try

=IF(OR(ISNUMBER(FIND({"Super","Account","Business" },B96))),"OK", "Not OK")

--
Regards,

Peo Sjoblom


"Micayla Bergen" wrote in message
...
Hi i am using the following formula to find text in a cell
=IF(ISNUMBER(FIND("Super",B96)),"OK", "Not OK") and return a value. i have
adapted the formula for another spreadsheet and simply added more values
to
check, i.e. =IF(ISNUMBER(FIND("Super""Account""Business:,B96)) ,"OK", "Not
OK") but it doesnt seem to work. where i can see the value in the cell it
is
not returning ok instead of not ok. is there a limit to the number of
values
i can search and if so why doesnt it say as an error? what am i missing?
Thanks very much

Hi!

Make a list of the words you want to find:

A1 = Super
A2 = Account
A3 = Business

=IF(SUMPRODUCT(--(ISNUMBER(FIND(A1:A3,B96)))),"Ok","Not Ok")

Tips:

FIND is case sensitive, SEARCH is not:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A1:A3,B96)))),"Ok","Not Ok")

Both FIND and SEACH will fail in situations when these types of values are
being tested:

Super = Superstitious = Superbowl

Account = Accountant

You can make the formula a little more robust by looking for the words with
a space on either side:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(" "&A1:A3&" "," "&B96&" ")))),"Ok","Not
Ok")

This will help reduce false positives in that:

Super < Superstitious < Superbowl

Even using the above formula, it's still not 100% foolproof.

Biff

"Micayla Bergen" wrote in message
...
Hi i am using the following formula to find text in a cell
=IF(ISNUMBER(FIND("Super",B96)),"OK", "Not OK") and return a value. i have
adapted the formula for another spreadsheet and simply added more values
to
check, i.e. =IF(ISNUMBER(FIND("Super""Account""Business:,B96)) ,"OK", "Not
OK") but it doesnt seem to work. where i can see the value in the cell it
is
not returning ok instead of not ok. is there a limit to the number of
values
i can search and if so why doesnt it say as an error? what am i missing?
Thanks very much

Thanks very much, it worked like a dream - albeit a convoluted and
long-winded one!

"Rowan" wrote:

The find fuction will only let you search for a single string so you will
need to adapt your formula to something like:

=IF(OR(ISNUMBER(FIND("Super",B96)),ISNUMBER(FIND(" Account",B96)),ISNUMBER(FIND("Business",B96))),"OK ","Not OK")

(watch out for the line wrap)

Hope this helps
Rowan

"Micayla Bergen" wrote:

Hi i am using the following formula to find text in a cell
=IF(ISNUMBER(FIND("Super",B96)),"OK", "Not OK") and return a value. i have
adapted the formula for another spreadsheet and simply added more values to
check, i.e. =IF(ISNUMBER(FIND("Super""Account""Business:,B96)) ,"OK", "Not
OK") but it doesnt seem to work. where i can see the value in the cell it is
not returning ok instead of not ok. is there a limit to the number of values
i can search and if so why doesnt it say as an error? what am i missing?
Thanks very much

Sp why didn't you try my formula?

--
Regards,

Peo Sjoblom


"Micayla Bergen" wrote in message
...
Thanks very much, it worked like a dream - albeit a convoluted and
long-winded one!

"Rowan" wrote:

The find fuction will only let you search for a single string so you will
need to adapt your formula to something like:

=IF(OR(ISNUMBER(FIND("Super",B96)),ISNUMBER(FIND(" Account",B96)),ISNUMBER(FIND("Business",B96))),"OK ","Not
OK")

(watch out for the line wrap)

Hope this helps
Rowan

"Micayla Bergen" wrote:

Hi i am using the following formula to find text in a cell
=IF(ISNUMBER(FIND("Super",B96)),"OK", "Not OK") and return a value. i
have
adapted the formula for another spreadsheet and simply added more
values to
check, i.e. =IF(ISNUMBER(FIND("Super""Account""Business:,B96)) ,"OK",
"Not
OK") but it doesnt seem to work. where i can see the value in the cell
it is
not returning ok instead of not ok. is there a limit to the number of
values
i can search and if so why doesnt it say as an error? what am i
missing?
Thanks very much