Text & Regex FormulaGoogle Sheets V8 Engine Verified

Google Sheets REGEXEXTRACT: Extract Numbers, Emails & Text via Regex

REGEXEXTRACT extracts matching substrings from a text string according to Google RE2 regular expression patterns. Ideal for isolating email addresses, SKU codes, domain names, and phone numbers from unstructured notes.

Formula Syntax:=REGEXEXTRACT(text, regular_expression)
💡 Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

REGEXEXTRACT uses regular expressions to extract matching substrings from a text cell. It parses strings based on pattern syntax (e.g., extracting domain names from email addresses, pulling order numbers from transaction strings, or isolating area codes from phone numbers). When capture groups () are used, REGEXEXTRACT can return multiple extracted segments across adjacent columns. REGEXEXTRACT uses Google RE2 regular expression syntax, which executes with predictable linear time performance to protect against catastrophic backtracking. When capturing multiple distinct pattern groups within parentheses, REGEXEXTRACT automatically spills results horizontally across adjacent columns, eliminating the need for nested text parsing formulas.

Formula Comparison: REGEXEXTRACT vs. Alternatives

Compared to nested LEFT, RIGHT, MID, and FIND formulas, REGEXEXTRACT accomplishes in one formula what previously required 50-character legacy string manipulations. It uses the RE2 regular expression library.

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
textTextRequiredThe source string or cell coordinate.
regular_expressionText (Regex)RequiredThe regular expression pattern enclosed in quotes.

Real-World Spreadsheet Examples

Explore verified operational formulas used in enterprise reporting and database workflows:

Example 1: Extract Email Address from Support Note

=REGEXEXTRACT(A2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")
How it works: Pulls the email address from strings like "Contact customer at [email protected] immediately".

Example 2: Extract Root Domain from Full URL

=REGEXEXTRACT(A2, "^(?:https?:\/\/)?(?:www\.)?([^\/\?#]+)")
How it works: Converts "https://www.googlesolution.com/tools/json" to "googlesolution.com".

Example 3: Extract Digits Only (Invoice Number)

=REGEXEXTRACT(A2, "\d+")
How it works: Pulls 984201 from "Invoice_INV-984201_Final".

Example 4: Extract Domain Name from Email Address

=REGEXEXTRACT(A2, "@(.+)")
How it works: Extracts everything following the @ symbol into a clean domain string.

Example 5: Extract Order Number and Date from Invoice Memo

=REGEXEXTRACT(A2, "Order #([0-9]+) on ([0-9]{4}-[0-9]{2}-[0-9]{2})")
How it works: Uses two capture groups to split order ID and date into two separate adjacent columns.

Common Errors & Solutions

❌ #N/A Function REGEXEXTRACT parameter 2 did not match
Resolution: The pattern did not match text. Wrap in IFERROR: =IFERROR(REGEXEXTRACT(A2, "\d+"), "None").
❌ #N/A Could not extract text from cell
Resolution: The pattern did not match the cell content. Wrap in IFERROR: =IFERROR(REGEXEXTRACT(A2, pattern), "No match").
❌ Unescaped special regex characters
Resolution: To match literal dots, brackets, or pluses, escape them with backslashes: \., \[.

Performance Pro Tips

  • Use capture groups `()` to extract specific parts of a pattern without including surrounding prefix characters.
  • Combine with ARRAYFORMULA to clean thousands of rows simultaneously: `=ARRAYFORMULA(IF(A2:A="", "", IFERROR(REGEXEXTRACT(A2:A, "\d+"))))`.
  • Always wrap REGEXEXTRACT in IFERROR or IFNA to prevent #N/A calculation halts across entire columns when unmatching rows are processed.
  • To extract numbers only from messy text strings, use the regex pattern "\d+" or "([0-9]+)" to pull out continuous integer blocks.

Frequently Asked Questions: REGEXEXTRACT

Can REGEXEXTRACT be used with ARRAYFORMULA?

Yes: =ARRAYFORMULA(IF(A2:A="", "", REGEXEXTRACT(A2:A, "[0-9]+"))).

How do I extract only numeric digits from a string?

=REGEXEXTRACT(A2, "\d+") or =REGEXEXTRACT(A2, "[0-9]+").

How do I extract text inside parentheses?

=REGEXEXTRACT(A2, "\(([^)]+)\)").

Is REGEXEXTRACT case-sensitive?

Yes. Use the (?i) flag at the start of the pattern for case-insensitivity: "(?i)urgent: (.+)".

Is REGEXEXTRACT available in Microsoft Excel?

Recent Excel 365 beta builds have added REGEXTRACT, but Google Sheets has natively supported it for over a decade.

Why does REGEXEXTRACT return #N/A when searching numbers?

REGEXEXTRACT requires text input. If cell A1 contains a raw number or date, wrap it in TO_TEXT(A1) or & "" before passing to REGEXEXTRACT.

Related Google Sheets Formulas