search(find_text,within_text) and OR(logical1,logical2)

  • Thread starter Thread starter Tiziano Bianchi
  • Start date Start date
T

Tiziano Bianchi

I need to see if a line of text (in cell A1) includes a specific text string
OR another one.
I came up with the following formula:
=IF(OR(SEARCH("string1",A1,1),SEARCH("string2",A1,1)),"found!","not found!")

This does not seem to work because the only result I get is #VALUE! even
when
"string1" or "string2" is present in the line of text.

Thanks in advance for any suggestions.
 
One way:

=NOT(AND(ISERR(SEARCH("string1",A1)),ISERR(SEARCH("string2",A1))))

Unless A1 contains both "string1" and "string2", your OR() will have
an error term.
 
One way:

=NOT(AND(ISERR(SEARCH("string1",A1)),ISERR(SEARCH("string2",A1))))

Unless A1 contains both "string1" and "string2", your OR() will have
an error term.
Click to expand...
...

Another way using only two levels of nested function calls.

=ISNUMBER(SEARCH("string1",A1)+SEARCH("string2",A1))

Then there's minimalism

=(COUNTIF(A1,"*string1*")*COUNTIF(A1,"*string2*")>0)

but it only works if the string being searched is a cell's entire text value.
 
That only returns true if both "string1" and "string2" exist in A1.

But

=IF(ISNUMBER(SEARCH("string1",A1))+ISNUMBER(SEARCH("string2",A1)),
"found", "not found")

is still two less level of nested function calls than mine was.
 
Back
Top