CODEWITHSUNDEEP
regexgoogle-sheetsstring-matchinggoogle-sheets-formulaarray-formulas
trippeljojo
trippeljojo

Reputation: 65

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.

Upvotes: 1

Views: 439

Answers (3)

trippeljojo
trippeljojo

Reputation: 65

In case anyone is searching for a short solution, I found that this formula works:

=REGEXMATCH(A2,B2&"(,|$)")

Upvotes: 0

player0
player0

Reputation: 1

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

0

Upvotes: 1

pnuts
pnuts

Reputation: 59475

Please try:

=REGEXMATCH(A2,"\b"&B2&"\b")

REGEXMATCH. \b denotes an ASCII word boundary.

Upvotes: 1

Related Questions