Regex match exact item in Google sheets

Question

I'm trying to use the regexmatch function in Google Sheets to match the item "hip hop" in the bolded cases in the lists below:

- atl hip hop, dirty south rap, hip hop, pop rap

- hip hop, dirty south rap, pop rap

so what I'm after is basically standalone hip hop in between commas (or at the beginning or end of string) - without any prefix or postfix.

Here's my formula but its not matching correct:

=REGEXMATCH(LIST,"(\\W|^)(,\\ship\\shop,)(\\W|$)") 

Answer 1

You need to use

=REGEXMATCH(LIST,"^(?:.*,\s*)?hip\shop(?:\s*,.*)?$") 

See the regex demo.

Note the ^ and $ anchors are only present for ease of debugging on external sites, since REGEXMATCH requires a full string match anyway.

Details

• ^ - start of string
• (?:.*,\s*)? - an optional occurrence of
  • .* - any zero or more chars other than line break chars as many as possible
  • , - a comma
  • \s* - zero or more whitespaces
• hip\shop - hip, a whitespace, hop
• (?:\s*,.*)? - an optional occurrence of zero or more whitespaces, a comma and any zero or more chars other than line break chars as many as possible
• $ - end of string.