Google Sheets Regular Expression and Replacement Syntax

Google Sheets is Google’s online spreadsheet application. Though it runs in your browser, it does not use your browser’s regex engine. It uses Google’s RE2 regex engine, with one important modifications. Google Sheets does not support the \p syntax for matching Unicode categories and scripts.

The regular expressions tutorial and reference on this website do not mention Google Sheets specifically. Follow what is written about RE2 instead. You only need to remember that Unicode categories and scripts are not available. None of Google Sheets’ regex functions allow you to pass flags outside the regex. So the regex is always case sensitive and the dot does never matches newlines.

Google Sheets uses its own replacement text syntax rather than the one provided by RE2. The replacement text tutorial and reference mention Google Sheets specifically. Its replacement text syntax is unique.

Google Sheets REGEX Functions

REGEXMATCH(text, regular_expression) returns TRUE or FALSE depending on whether regular_expression can find a match in text. Partial matches are allowed. Both parameters must provide text as input. Attempting to use it on a number results in a #VALUE! error. An invalid regular expression results in a #REF! error.

REGEXEXTRACT(text, regular_expression) returns the part of text that can be matched by regular_expression if the regex does not have any capturing groups. If the regex has one capturing group then it returns the match of that group. If the regex has multiple capturing groups then it returns the matches of all capturing groups, filling as many columns as there are groups. Non-participating groups result in empty cells. If the regular expression cannot find a match in text then the error #N/A is returned. You can use IFNA(REGEXEXTRACT(text, regular_expression), "") if you’d rather have an empty cell when REGEXTRACT cannot find a match. Numeric input results in a #VALUE! error and an invalid regex in a #REF! error.

REGEXTRACT() only considers the first regex match. It does not look for additional matches. Google Sheets does not provide a function to extract all regex matches.

REGEXREPLACE(text, regular_expression, replacement) replaces all occurrences of regular_expression in text with replacement, returning the modified string as its result. If the regex cannot find any matches then text is returned unchanged. There is no option to replace only the first match. Numeric input results in a #VALUE! error and an invalid regex in a #REF! error.

The replacement text supports backreferences, but the syntax is completely different from RE2. While RE2 uses backslashes to form backreferences, Google Sheets uses dollar signs. 0ドル inserts the overall regex match and 1ドル to 999ドル insert the text matched by a capturing group. A backreference to a group that does not exist results in the error #N/A. Trailing digits are taken as literals if the shorter number results in a valid backreference. 1ドル23 is interpreted as a backreference to the first group followed by the literal 23 if there are fewer than 12 capturing groups in the regex.

A backslash escapes any character that follows, including the dollar sign and the backslash itself. A dollar sign that is not followed by a digit is a literal dollar sign. So you only need to escape literal backslashes and dollar signs followed by digits if you want those to be literals.