find formula and return value

M

Micayla Bergen

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
 
R

Rowan

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
 
P

Peo Sjoblom

Try

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

Biff

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
 
M

Micayla Bergen

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