Google Sheets IF/Find formula help

[Greg Bernhardt]

TL;DR

Trying to efficiently create a formula to find string in a cell and if true then return a string.

Say cell A11 contains the text "power tools". I want B11 to display if A11 contains the word "tools".

I choose to use IF() and FIND(). If "tools" does exist it returns "Yes Tools" fine, but if not it returns an error saying FIND() didn't match a value instead of displaying "No Tools". I got around this by wrapping it all in IFERROR(). This works, but I can't imagine it's efficient and there must be a better way.

=IFERROR(IF(FIND("tools",A11),"Yes Tools","No Tools"), "No Tools")

 

[pbuk]

FIND() never returns a false value so there is no point using it directly in an IF statement, instead use:

=IF(ISERROR(FIND("tools",A11)),"No Tools","Yes Tools")

(as an aside, in environments with zero indexed strings your code would return "No Tools" if the string in A11 started with "tools" - a common bug caused by lazy programming and inadequate unit testing)

 

[Wrichik Basu]

Both FIND() and SEARCH() return the location of substring if the search is true, otherwise a value error. So try IF(ISNUMBER(FIND("tools",A11)),"Yes Tools","No Tools").

 

[Greg Bernhardt]

Thanks! Both of those work! Perhaps ISNUMBER() is better best practice?

=IF(ISERROR(FIND("tools",A11)),"No Tools","Yes Tools")

=IF(ISNUMBER(FIND("tools",A11)),"Yes Tools","No Tools")

 

[Wrichik Basu]

Perhaps ISNUMBER() is better best practice?

Basically none of these approaches is good from a programmer's point of view. When I want to search for a substring within a string, I would want the function to either return a boolean value, or a numeric value. For example, in Java, contains() returns boolean, while indexOf() returns -1 if the substring is absent. But they don't throw an exception if the search is unsuccessful. This is something found only in Excel/Google sheets and similar software.

 

[pbuk]

Thanks! Both of those work! Perhaps ISNUMBER() is better best practice?

I can't see any reason to prefer either over the other.