Can I use regular expressions in a spreadsheet formula?
Yes. Grid by Zaweba has REGEXTEST, REGEXEXTRACT and REGEXREPLACE, so formulas can check, pull out and rewrite text by pattern, offline and free.
Yes. Grid by Zaweba has three pattern-matching functions in its Text family: REGEXTEST checks whether text matches a pattern, REGEXEXTRACT pulls out the part that matches, and REGEXREPLACE swaps matching text for something else. They work like any other formula, recalculating when the data changes, and the spreadsheet is free and works offline.
What a regular expression is
A regular expression is a short description of a pattern in text. Instead of looking for one fixed string, you describe its shape: some digits, then a hyphen, then two letters. That's the difference between finding the word Invoice and finding anything that looks like an invoice number, wherever it sits in the cell.
Patterns have a compact syntax. A backslash d means a digit, a plus sign means one or more, square brackets list allowed characters, and so on. It looks cryptic at first, but a handful of pieces covers most everyday jobs.
Checking text with REGEXTEST
REGEXTEST returns TRUE or FALSE. Use it to flag entries that don't look right: a postcode column where some entries are missing their second half, phone numbers with letters in them, or reference numbers in the wrong format. Combined with conditional formatting through a formula-based rule, it can colour every cell that fails the test, so problems stand out on screen.
It also works inside FILTER. A FILTER that keeps only rows where REGEXTEST is TRUE gives you, say, every order line whose description mentions a size code.
Pulling out matches with REGEXEXTRACT
REGEXEXTRACT returns the text that matches the pattern. That's the tool for picking an order number out of an email subject line, a price out of a product description, or a date written in a known style out of a sentence. Where TEXTBEFORE and TEXTAFTER need a fixed marker, REGEXEXTRACT just needs to know what the thing you want looks like.
Results are text. If you extract a number you want to add up, wrap the formula in VALUE.
Rewriting with REGEXREPLACE
REGEXREPLACE changes every part of the text that matches the pattern. It can strip out all non-digit characters from phone numbers so they're consistent, collapse runs of spaces into one, or reformat a code by capturing its parts and putting them back in a new order.
Because it's a formula, the original text stays untouched in its own column. When you're happy with the cleaned version, copy it and use Paste Special to paste values only if you want to replace the original.
Getting patterns right
Build up slowly. Start with a simple pattern on a few sample cells, check the results, then make it stricter. Grid's Function Arguments dialog shows a live result as you fill in each argument, which is a quick way to try a pattern before copying the formula down.
Watch out for characters that have special meaning in patterns, such as the full stop, brackets and the plus sign. To match one literally, put a backslash before it.
And don't reach for a regular expression when a simpler function will do. TEXTBEFORE, TEXTAFTER and TEXTSPLIT are easier for the next person to read when the text has a clear separator.
If you'd like help writing one
If you've connected your own AI account to Grid, Write a formula... lets you describe what you want in a sentence. Grid checks any suggested formula with its own engine before offering to insert it, and Explain this formula will talk you through a pattern someone else wrote. Nothing is sent unless you ask.
Regular expressions turn a lot of manual tidying into a single formula. Grid by Zaweba gives you all three, ready to use in any sheet.
