Google Sheets IF/Find formula help

  • #1
18,037
7,391

Summary:

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

Main Question or Discussion Point

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.

Code:
=IFERROR(IF(FIND("tools",A11),"Yes Tools","No Tools"), "No Tools")
 
Last edited:

Answers and Replies

  • #2
pbuk
Science Advisor
Gold Member
1,235
262
FIND() never returns a false value so there is no point using it directly in an IF statement, instead use:
Code:
=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)
 
  • #3
1,322
1,129
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").
 
  • #4
18,037
7,391
Thanks! Both of those work! Perhaps ISNUMBER() is better best practice?

Code:
=IF(ISERROR(FIND("tools",A11)),"No Tools","Yes Tools")
Code:
=IF(ISNUMBER(FIND("tools",A11)),"Yes Tools","No Tools")
 
Last edited:
  • #5
1,322
1,129
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.
 
  • #6
pbuk
Science Advisor
Gold Member
1,235
262
Thanks! Both of those work! Perhaps ISNUMBER() is better best practice?
I can't see any reason to prefer either over the other.
 

Related Threads for: Google Sheets IF/Find formula help

  • Last Post
Replies
4
Views
701
Replies
1
Views
618
Replies
4
Views
2K
Replies
11
Views
8K
Replies
18
Views
4K
Replies
12
Views
3K
  • Last Post
Replies
9
Views
2K
  • Last Post
Replies
5
Views
2K
Top