REGEXEXTRACT Function

Pulls out the text that matches a regular expression pattern.

Category

Text

Available in

Microsoft 365

Returns

Text, or an array that spills

Want to practice REGEXEXTRACT in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

REGEXEXTRACT finds text that fits a pattern and returns it — a phone number buried in a note, an order number in an email subject.

Return mode 1 returns every match and mode 2 returns the parts of the first match you've wrapped in brackets. Both spill across the row.

Syntax

=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])

Arguments

ArgumentRequired?What it does
textYesThe text to search.
patternYesThe regular expression describing what to extract.
return_modeOptional0 = first match (default), 1 = all matches, 2 = capture groups of the first match.
case_sensitivityOptional0 = case-sensitive (default), 1 = ignore case.

Examples

Example 1 — Phone numbers from notes

Each CRM note mentions a phone number somewhere.

B2fx=REGEXEXTRACT(A2, "\d{3}-\d{3}-\d{4}")
AB
1NotePhone
2Call Ana on 604-555-0199 today604-555-0199
3Ben (403-555-0142) prefers email403-555-0142
=REGEXEXTRACT(A2, "\d{3}-\d{3}-\d{4}") Explain

Example 2 — Every order number

Return all order numbers mentioned in an email.

B2fx=REGEXEXTRACT(A2, "ORD-\d+", 1)
ABCD
1EmailOrder 1Order 2Order 3
2Re: ORD-1001, ORD-1007 and ORD-1012ORD-1001ORD-1007ORD-1012
=REGEXEXTRACT(A2, "ORD-\d+", 1) Explain

Example 3 — Splitting with capture groups

Pull the last and first names out of "Lopez, Ana".

=REGEXEXTRACT(A2, "(\w+), (\w+)", 2) Explain

Common mistakes

  • #N/A when nothing matches. Wrap in IFERROR or IFNA to show a friendly message.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.