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|$)")
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 whitespaceship\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.