Search code examples
regexgoogle-sheetsstring-matchinggoogle-sheets-formulaarray-formulas

How can I find exact occurences of a string in another string in Google Sheets?

I have one column (coming from google forms) containing a comma-separated list of names and I want to check if a specific name is included in this list.

There are several functions (match, search, find, etc.), I tried and it kind of worked up to a certain point. The problem arises as soon as the list contains a name like "Carolin" and the name that I want to check is "Caro".

Then it always returns "true" which is logically correct because the string "Carolin" obviously contains the string "Caro".

In my case, I want to distinguish between "Carolin" and "Caro" as shown in the example. How can I do that?

names                            | name to check   | expected result
-----------------------------------------------------------------------
Michael, Carolin, John, Jane     | John            | True
Michael, Carolin, John, Jane     | Sarah           | False
Michael, Carolin, John, Jane     | Carolin         | True
Michael, Carolin, John, Jane     | Caro            | False
Michael, Carolin, John, Jane R.  | Jane R.         | True
Michael, Carolin, John, Jane R.  | Jane            | False

Edit: Added two lines in the example. I forgot about another special case.

Solution 
  • =ARRAYFORMULA(IFERROR(REGEXMATCH(JOIN( ,
 REGEXMATCH(TRIM(SPLIT(A1, ",")), "^"&B1&"$")), "T")))

    0