Google Sheets · regular expressions
Your regex works.
Google Sheets disagrees.
Sheets runs RE2. Every regex tester you have ever used runs something else. A pattern can pass every check you know how to make and still fail in a cell — and the error it gives you points at the wrong thing entirely.
Paste one in. It will tell you exactly which part a cell will refuse, and why.
That pattern is fine in JavaScript. It is fine in Python. It is fine on regex101. In a Google
Sheet it is #REF!, and we have
the execution log to prove it.
Three things nobody tells you
- The error points at the wrong thing
-
A bad pattern does not give you
#ERROR!. It gives you#REF!— the error everyone knows as "you deleted a cell I was pointing at." So you go hunting through your references while the regex sits there, quietly wrong. We confirmed it twice: the cell shows#REF!, andERROR.TYPE()returns 4. - Lookaround does not exist
-
(?=…)and(?<=…)are the first thing people reach for and the first thing RE2 throws away. It drops them on purpose: without them, matching can never take exponential time on hostile input. You get speed. You lose lookaround. \1fails without failing-
In
REGEXREPLACE, Sheets wants$1. Write"\2 \1"and you get no error at all — the backslashes vanish and the cell fills with the literal text2 1. We ran it. That is what comes out. An error would have been kinder.
Why you can believe any of this
Every rule the tester enforces was run as a formula in a live Google Sheet, and the value Google returned was recorded. Not simulated. Not remembered from documentation. Executed, then written down, with the date and the version of everything involved.
The runs are on this site. So is the code that made them. If a formula here stops working — because Google changed something, or because we were wrong — there is a report button, and the fix and its date go on the page.
When a new pattern gets verified, I'll tell you
One email when a new formula is executed, logged, and published. Nothing else. Unsubscribe in one click.