17

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 ?????? with in the above formula OR if this formula is possible. Any ideas on how to accomplish the desired outcome is greatly appreciated.

|improve this question
23

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.

|improve this answer
  • 8
    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 at 17:17
  • The if is there so they can convert the true to a 1 or 0 if false – Aurielle Perlmann Jan 18 at 17:18
6

=if(search("sheets",B2)>0,1,0)

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

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

|improve this answer
  • 2
    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
1

A solution without REGEX:

=if(iserror(FIND("sheets",B2)),0,1)

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

|improve this answer
  • 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 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.