Why =isnumber(search is function is returning TRUE when the substring is blank? And how to I remedy this discrepancy?

Cell E2 is blank cell. There is no space or hidden character. So why is =ISNUMBER(SEARCH(E$2,$A3)) returning True?

CodePudding user response：

You need to first check if E2 is blank and then proceed with ISNUMBER if true

=IF(ISBLANK(E$2), FALSE, ISNUMBER(SEARCH(E$2,$A3)))

CodePudding user response：

SEARCH will interpret blanks as empty strings.

Another approach to what it looks like you're trying to do is to replace the substring with an empty string and see if the length changes:

=LEN($A2)>LEN(SUBSTITUTE($A2,E$1,""))

CodePudding user response：

So, two variations, one as per the other answer but the other using find().

For the first, it returns a blank. For the second with find() then iferror will deal with the value error.