ysskrishna logoysskrishna

Chapter 6 of 7 · 3 min

Custom regex extraction

When the presets do not fit, write your own pattern. Six patterns you will reuse, how to test them, and the mistakes that return nothing.

Choose Custom regex in Extract's MATCH PATTERN and type your own regular expression. The listing says it is a global match: every match in the cell is returned and joined with your delimiter, exactly like the presets.

Six patterns worth keeping

GoalPatternMatches in "Order INV-000731 for ₹4,500 on 17-11-2026, ref #A12"
Invoice numbersINV-\d{6}INV-000731
Amounts with separators\d{1,3}(,\d{3})*(\.\d+)?000, 731, 4,500, … (too greedy, see below)
Indian-style amount after ₹(?<=₹)[\d,]+4,500
Ticket keys[A-Z]{2,10}-\d+INV-000731
Domains from URLs(?<=://)[^/\s]+- (none in this text)
Words in caps (acronyms)\b[A-Z]{2,}\bINV

The second row shows the most common problem: a pattern that is too loose matches parts of other things. Anchor patterns to context (a prefix like ₹, a word boundary \b, a fixed length) to make them precise.

Regex flavour

Google Apps Script add-ons run JavaScript, so expect JavaScript regular expression syntax: \d, \w, \b, character classes, {n,m}, groups and lookahead.

Differences from Sheets' own REGEXEXTRACT (which uses RE2):

  • RE2 has no lookbehind; JavaScript does.
  • REGEXEXTRACT returns the first match (or capture groups); the add-on returns all matches.

Testing a pattern before you apply it

  1. Copy 5-10 representative cells, including awkward ones, to a scratch column.
  2. Apply the pattern there.
  3. Compare with what you expected. Too many matches: add anchors or boundaries. Too few: loosen a quantifier or allow optional parts with ?.
  4. Only then run it on the full (copied) column.

For a quick check without the add-on, =REGEXEXTRACT(A2,"INV-\d{6}") in a spare cell confirms a pattern matches at least once (RE2 syntax, no lookbehind).

Mistakes that return nothing

  • Spreadsheet-style wildcards. * is not "anything"; use .* or, better, a specific class.
  • Unescaped special characters. To match a literal ., +, ( or $, escape it: \., \+, \(, \$.
  • Case. inv-\d+ will not match INV-… unless matching is case-insensitive. Use [Ii][Nn][Vv] or match the case your data uses.
  • Hidden characters. Non-breaking spaces from web pages look like spaces but don't match . Use \s.

Step by step: invoice numbers from email subjects

  1. Copy your subjects column as values next to the original.
  2. Select the copy, open Extract, choose Custom regex.
  3. Enter INV-\d{6}. Set delimiter ,. Tick Unique matches only. Click Apply.
  4. Filter the column for empty cells to find subjects that had no invoice number.

Practice: invoice numbers

Sample subjects:

SubjectExpected with INV-\d{6}
Re: INV-004512 overdueINV-004512
Invoices INV-000101 and INV-000102INV-000101 and INV-000102, joined by your delimiter
inv-123456 resendNothing, because the pattern is upper case
INV-12345 (old format)Nothing, because only five digits

The third row is the classic mistake: regular expressions are case-sensitive by default. If lower-case IDs should match, use [Ii][Nn][Vv]-\d{6}, or make the column upper case first with the Case tab.

Test with a formula first

Before applying a pattern to a whole column in place, try it in a helper cell with =REGEXEXTRACT(A2, "INV-\d{6}"). Sheets uses the RE2 regex engine for its REGEX functions, so a pattern that works there avoids lookbehind and backreferences, which RE2 does not support. The add-on may accept more syntax than RE2 (see the regex flavour note above), but a pattern that passes in REGEXEXTRACT is the safe common subset.

Quick check

  1. How do you match a literal dollar sign?Escape it: \$.

  2. Why does \d+ return too much from an order line?It matches every run of digits. Anchor it to context, such as a prefix or a fixed length.

  3. What does "global match" mean for a cell with three invoice numbers?All three are returned, joined by the delimiter.

Continue exploring

ProductGoogle WorkspaceGoogle Sheets Text Power Tools

Google Sheets Text Power Tools

Fix messy sheet text in place. Case, trim, strip, and extract without leaving the grid.
Google Sheets Text Power Tools
Text & Data Conversion
Google Workspace Add-ons
Google Sheets
ProductGoogle WorkspaceGoogle Sheets Tab Manager

Google Sheets Tab Manager

Your workbook outgrew the tab bar. Clean it up in batches.
Google Sheets Tab Manager
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Import Markdown, JSON, HTML and CSV tables into Google Sheets without broken columns, and export any range as clean Markdown, JSON, HTML or CSV.

Google Sheets Table Converter
Google Sheets
+1

6 chapters · 16 min

BlogCover image for How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

Google Sheets Tab Manager
Google Workspace Add-ons
+1
2 min read
ProductGoogle WorkspaceGoogle Sheets Merge Tools

Google Sheets Merge Tools

Repeatable merges in Google Sheets, without the misclick marathon.
Google Sheets Merge Tools
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Delete multiple tabs in Google Sheets at once, hide and unhide in bulk, rename with find and replace, colour and sort tabs A–Z with the free Sheets Tab Manager.

Google Sheets Tab Manager
Google Sheets
+1

6 chapters · 24 min