Nancy Huynh

Get all matches with regex in Google Sheets

Problem: Regex extract only returns the first match

The REGEXEXTRACT function will extract the first matching substrings according to a regular expression, not all the substrings.

Solution: Use regex replace function

The REGEXREPLACE function can be used as a workaround to extract all substrings according to a regular expression. By replacing the string with the capture group instead of other text, we can extract all the substrings that match the regular expression.

REGEXREPLACE(A5,".?((?i)coffee)|.", "$1 ")

Note that in the regex above, the (?i) is for case insensitive

Problem: Match entire word, not part of a word

I wanted to match for the word "tea", but not words that include "tea" in it like "tears"

Solution: Use word boundaries

The regex for word boundaries is \b