# Google Sheets formula for “if contains”

I'm trying to figure out how to identify `IF` a list of items in one cell contains 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 not sure 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.

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 '16 at 1:09
• The "if" isn't needed since regexmatch already returns a boolean, so just "REGEXMATCH(A1, "sites")" – David Jan 18 '19 at 17:17
• The if is there so they can convert the true to a 1 or 0 if false – Aurielle Perlmann Jan 18 '19 at 17:18
• If parameters should be semicolon, not comma: `=IF(REGEXMATCH(A1, "sites"); 1; 0)` – Emil Stenström Dec 17 '19 at 12:35
• The semicolon delimiter is dependent on your locale – Aurielle Perlmann Dec 19 '19 at 19:49

`=if(search("sites",B2)>0,1,0)`

`=if(IFERROR(search("sites",B2)>0,0),1,0)`

Both work. The second writes a 0 if there's an error.

• 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 '16 at 23:05
• How do I use `search` with `COUNTIF`? – Aaron Franke Feb 8 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 '19 at 19:04

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.

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? – Adam Starrh Sep 11 '19 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. – Ryan Shillington Sep 11 '19 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,"*"))`

``=IF(QUERY(A1, "where A contains 'sites'")<>"", 1, )`` This will return TRUE or FALSE:

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