# Google Sheets formula for "if contains"

I'm trying to figure out how to identify `IF` a list of items in one cell containing a value or string.

EXAMPLE

Cell `A1` contains `sites, sheets, docs, slides`.

I want cell `B1` to display a 1 'if' cell `A1` contains the string `sites`.

FORMULA

``````=if(A1 ?????? "sites", 1,0)
``````

I'm unsure what to replace the `??????` within the above formula OR if this formula is possible. Any ideas on how to accomplish the desired outcome are greatly appreciated.

• If you want to colorize every row that contains a word, see here: janac.medium.com/… Jan 18, 2021 at 17:29

You can use `REGEXMATCH`:

``````=IF(REGEXMATCH(A1, "sites"), 1, 0)
``````

To explain, `REGEXMATCH` returns true if and only if the argument is a substring of your string.

• Using `regexmatch` would be simpler: `if(regexmatch(A1, "sites"), 1, 0)`.
– user79865
Jul 19, 2016 at 1:09
• The "if" isn't needed since regexmatch already returns a boolean, so just "REGEXMATCH(A1, "sites")" Jan 18, 2019 at 17:17
• The if is there so they can convert the true to a 1 or 0 if false Jan 18, 2019 at 17:18
• If parameters should be semicolon, not comma: `=IF(REGEXMATCH(A1, "sites"); 1; 0)` Dec 17, 2019 at 12:35
• "REGEXMATCH returns true if and only if the argument is a substring of your string." this is a highly misleading statement. Strings are interpreted as regular expressions so there are many cases where this does not work. Jun 17, 2020 at 19:11

If you, as Ryan Shillington suggests, want to know the number of cells within a range that has "sites" in it, you might try:

``````=COUNTIF(A1:A100;"*sites*")
``````

The * acts as a wildcard notion, so it will look for a match anywhere within the cell, not just an exact match for the whole cell.

I've also done this with a cell reference instead of "sites", but then you have to add wildcard-asterixes to the text in the cell.

• I haven't tested it but I expect you could add the asterisks in the formula when referencing a cell by doing `=COUNTIF(A1:A100; CONCATENATE("*", B1, "*"))`
– Matt
Dec 17, 2020 at 18:43
• I find this to be the simplest solution and can confirm that it works. Mar 1, 2021 at 17:49

This returns a `#VALUE!` error if the search term isn't found:

``````=IF(SEARCH("sites", B2) > 0, 1, 0)
``````

This uses the same logic but catches the error and returns a `1`/`0` for success/failure:

``````=IF(IFERROR(SEARCH("sites", B2) > 0, 0), 1, 0)
``````
• The first formula will never return 0 because `SEARCH(search_for, text_to_search, [starting_at])` will return an error when `search_for` do not be found.
– Rubén
Jul 15, 2016 at 23:05
• How do I use `search` with `COUNTIF`? Feb 8, 2020 at 0:10

A solution without REGEX:

``````=if(iserror(FIND("sites",B2)),0,1)
``````

capitalization matters and `if` logic must be reversed, given that answer is `true` when the string is not found.

• This is basically the same as this answer, but introducing the case sensitive thing. By the way the sample data provided by the OP includes `sheets` not `Sheets`.
– Rubén
Jan 16, 2019 at 19:04

This will return TRUE or FALSE:

`=IFERROR(SEARCH("keyword",A1)>0, FALSE)`

You're probably doing this because you want to know the count or average # of rows that have the word "sites" in it. Assuming you have 100 rows of entries in column A, you can use this function to find out how many rows have the word "sites" in it:

``````=QUERY(A1:A100, "select count(A) where A like '%sites%'")
``````

This is much faster than creating a column of 1s and zeros and then operating on that new column.

• Can this work over a box instead of just a column? Sep 11, 2019 at 18:53
• Yup. instead of `A1:A100` you could make it `A1:C100` and then write a query with columns A, B and C. Sep 11, 2019 at 19:30

If you go with the asterisks option and want a cell reference, you can use the CONCATENATE function to add the asterisks

`=COUNTIF(A1:A100,CONCATENATE("*",A1,"*"))`

• `CONCATENATE` can be harder to read than using `&` to join the strings: `=COUNTIF(A1:A100, "*" &A1 &"*"))` Feb 13, 2021 at 8:46

``=IF(QUERY(A1, "where A contains 'sites'")<>"", 1, )`` `Countif` is a memory hog and a lot of these answers depend on `not true` type approach (iferror etc.). While that works, in a general better approach to conditions is seeking what is `true`.

Case not sensitive:

``````=isnumber(SEARCH("sites",A1))
``````

Case sensitive:

``````=isnumber(SEARCH("sites",A1))
``````

This will return `TRUE` if a match, or `FALSE` if not.