REGEXEXTRACT function
Quick answer
REGEXEXTRACT pulls out the part of a text that matches a regular expression: an order number, an email domain, the digits in a messy code.
- Syntax
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])- Most common use
=REGEXEXTRACT(A2,"\d+")returns 48213 (The first run of digits, as text)
By default it returns the first match; return_mode 1 returns every match and 2 returns the capture groups. It returns #N/A when nothing matches.
- Available in
- Microsoft 365 only
- Category
- Text functions
- Returns
- Text, or an array of text for return_mode 1 and 2; #N/A if nothing matches.
- Google Sheets
- Google Sheets has REGEXEXTRACT with two arguments: it returns the first match, or the capture groups when the pattern has them.
Syntax
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text to search. |
pattern | Required | The regular expression, in quotes. |
return_mode | Optional | Optional: 0 (default) the first match, 1 every match as an array, 2 the capture groups of the first match. |
case_sensitivity | Optional | Optional: 0 (default) for case-sensitive, 1 to ignore case. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B2 | =REGEXEXTRACT(A2,"\d+") | 48213 | The first run of digits, as text |
| B3 | =REGEXEXTRACT(A3,"\d{3}-\d{4}",1) | 555-0142spills 1 × 2 | Every phone number, spilled across the row |
| B4 | =REGEXEXTRACT(A4,"@(.+)",2) | contoso.com | Only the capture group: the domain |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Note | Result | |
| Order 48213 shipped | 48213 | |
| Call 555-0142 or 555-0199 | 555-0142 | 555-0199 |
| ana.lopez@contoso.com | contoso.com |
Practice REGEXEXTRACT
Graded instantly, then re-checked on changed data.
Pull the invoice number
A2:A6 contains an invoice number: INV- followed by digits. In B2, extract the invoice number, including INV-. Fill down to B6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #N/A | Nothing matched. Check the case (matching is case-sensitive by default), or wrap it: =IFNA(REGEXEXTRACT(A2,"\d+"),""). |
| The number won't add up | REGEXEXTRACT returns text. Wrap it in VALUE, or use --REGEXEXTRACT(…), to get a number. |
| #SPILL! | return_mode 1 and 2 return several values that need empty cells to the right. |
Tips
- Capture groups in parentheses pick out just part of the match: =REGEXEXTRACT(A2,"ID: (\d+)",2) returns only the digits.
Questions
How do I extract numbers from text in Excel?
=REGEXEXTRACT(A2,"\d+") returns the first run of digits as text; =--REGEXEXTRACT(A2,"\d+") makes it a number.
How do I extract every match?
Set return_mode to 1: =REGEXEXTRACT(A2,"\d+",1) spills every run of digits across a row.
Answers that use REGEXEXTRACT
Updated Checked by running: every formula on this page was run by our formula engine on .