Why your regular expression works everywhere except Google Sheets

You found a pattern on Stack Overflow. You tested it on regex101, and it lit up green. You pasted it into a cell and Google Sheets said #REF!.

So you went looking for a broken reference. You checked whether you had deleted a column. You checked the sheet name. You rewrote the formula to point somewhere else, and it still said #REF!, and by then you had lost twenty minutes to a problem that was never about references at all.

There is no broken reference, and there is no typo. Google Sheets and every regex tool you have ever used are running different regular expression engines, they disagree about what a regular expression even is, and when they disagree, Sheets reports it with an error code that describes something else entirely.

Sheets runs RE2

Google's help page for REGEXMATCH contains one sentence that explains almost every problem people have with these functions:

Google products use RE2 for regular expressions. Google Sheets supports RE2 except Unicode character class matching.

RE2 is a regex engine that Google wrote and open-sourced. Its defining property is a guarantee: it will always finish, in time proportional to the length of your text, no matter what pattern you give it. That sounds obvious until you learn that the engines inside JavaScript, Python, Java, PHP and Perl offer no such promise. Hand them a pattern like (a+)+b and thirty as, and they can spin for longer than the universe has existed. It is a real class of denial-of-service attack, and it has taken down real websites.

Google runs regular expressions from millions of spreadsheets on its own servers. It cannot allow a formula in your budget tracker to occupy a machine for a week. So it uses an engine that mathematically cannot do that — and the price of the guarantee is that certain features have to go.

What had to go

The features RE2 gives up are, almost perfectly, the features people reach for first.

Lookaround. (?=…) and (?<=…) let you say "match this, but only if it is followed by that." They are how most people extract a number that comes after a word. They require the engine to try a match, back up, and try again — which is exactly the backtracking RE2 refuses to do. In a cell, both return #REF!.

Backreferences. (\w+)\s+\1 — "a word, then the same word again" — needs the engine to remember what it captured and compare against it mid-match. RE2 cannot. Confusingly, backreferences do exist in REGEXREPLACE, in the replacement text. They just are not written the way RE2's own documentation writes them. More on that below, because this is where even careful people get caught.

Possessive quantifiers, atomic groups, recursion, \K. All of them exist to control backtracking. In an engine that never backtracks, they are meaningless, so they are syntax errors.

The error tells you about the wrong problem

#REF! means, to anyone who has used a spreadsheet for more than a week, that a formula is pointing at a cell that no longer exists. It is the error you get after deleting a column. It has nothing to do with regular expressions, and it is what Sheets shows you when your regular expression is invalid.

We did not want to take one measurement's word for that, so we asked Sheets a second time, using its own function for interrogating errors:

=ERROR.TYPE(REGEXMATCH("abc123", "abc(?=123)"))   →   4

ERROR.TYPE returns 3 for #VALUE!, 7 for #N/A, 8 for #ERROR! — and 4 for #REF!. Two independent measurements, one answer. Google classifies an unsupported regular expression as a broken reference.

There is a real hint buried in the cell, but you have to know to look for it: hover over the red triangle and Sheets will tell you "…is not a valid regular expression." Almost nobody hovers. They see #REF!, and they start deleting things.

And it can hide from you completely

Here is the part that should worry anyone who has wrapped a formula in IFERROR to tidy up a report:

=IFERROR(REGEXMATCH("abc123", "abc(?=123)"), "caught")   →   caught

IFERROR swallows it. A pattern that Google cannot even parse becomes a clean, quiet fallback value, and the spreadsheet looks fine. It will keep looking fine for as long as nobody checks. If you have IFERROR wrapped around a REGEXEXTRACT somewhere in a monthly report, it is worth taking the wrapper off once and seeing what is underneath.

And one thing that went only in Sheets

Read that sentence from Google's docs again: "supports RE2 except Unicode character class matching."

RE2 itself supports \p{L} — "any letter, in any alphabet." It works in Go. It works in BigQuery. It works in every other place Google ships RE2. It does not work in Google Sheets, and Google says so in a single subordinate clause that almost nobody reads to the end of.

This is the cruellest one, because it catches the people who did their homework. If you looked up RE2's syntax before writing your pattern, you would have concluded that \p{L}+ was safe. It is not. In a cell it is #REF!, and the documentation that told you it would work was documentation for a different product.

The replacement string speaks another language

REGEXREPLACE has a trap that is genuinely difficult to reason your way out of, because two reasonable sources disagree.

RE2's own documentation writes a backreference in replacement text as \1. Google Sheets wants $1. So the correct way to swap two words is:

=REGEXREPLACE("John Smith", "(\w+) (\w+)", "$2 $1")

Write "\2 \1" instead and you do not get an error. You get this:

=REGEXREPLACE("John Smith", "(\w+) (\w+)", "\2 \1")   →   2 1

The backslashes vanish. What is left in the cell is the literal text 2 1. No red triangle, no error code, nothing to notice — just a column of numbers where names used to be. This is worse than #REF! in every way that matters, because an error at least tells you to stop.

There is one more edge in here. If your replacement refers to a group your pattern never captured — "[$1]" against a pattern with no parentheses — the cell returns #N/A. Not an empty string, not #REF!. A third error, for a third reason. The tool above counts your capture groups and says so before you paste.

The other half of the problem: quotes

Even a valid pattern fails if the formula around it is malformed, and one rule catches almost everybody. Inside a Google Sheets string, a double quote is written twice. A pattern that needs to match a quotation mark looks like this:

=REGEXEXTRACT(A1, """([^""]+)""")

That is not a mistake. It is one opening quote, two escaped quotes, and so on. Nothing about it is intuitive, which is why the tool at the top of this page writes the formula for you and escapes the quotes as it goes. Copy the line, paste it in a cell, and it works.

What this tool does, and what it cannot do

A browser cannot run RE2. It has JavaScript's regex engine and nothing else. So a tool that simply evaluates your pattern in the browser and shows you the result will happily give you a green light for (?<=Invoice )\d+ — and that green light is a lie. This is precisely what every general-purpose regex tester on the internet does to a Sheets user.

This tool does something different. It reads your pattern and looks for the constructs that a real Google Sheet rejected when we ran them. If it finds one, it shows you #REF!, even though the browser it is running in could evaluate the pattern without complaint. It reports the spreadsheet, not the browser.

Where it finds nothing to object to, it evaluates the pattern in JavaScript and says so, plainly, in the line under the result. Before it does, it rewrites your replacement string into the dialect JavaScript speaks — turning \2 into a bare 2 and $0 into the whole match — because those are the two places the engines differ, and we measured exactly how. The preview reproduces what the sheet returned. We check that in a test, every build.

Every rule in the list above was written as a formula, sent to Google's servers through the Sheets API, and its return value recorded with a timestamp. Not remembered from documentation. Not predicted by a model. Executed. Read the log — including the rows where the result surprised us.

See what a cell will actually do