The REGEXEXTRACT function in Excel pulls out the part of a text that matches a regular expression: =REGEXEXTRACT(A2,"\d+") returns the first number in a cell, and =REGEXEXTRACT(A2,"[\w.+-]+@[\w-]+\.\w+") returns an email address. The third argument sets what to return: the first match (0), all matches as an array (1) or the capture groups in parentheses (2). If there’s no match, the function returns #N/A, and the result is always text — add a double minus to get a number. The function is available in Excel for Microsoft 365.
| Function category | Text functions |
| Excel versions | Excel for Microsoft 365 (Windows and Mac) |
| Volatility | Non-volatile |
| Similar functions | REGEXTEST, REGEXREPLACE, MID, SEARCH |
REGEXEXTRACT function syntax
=REGEXEXTRACT(text,pattern,[return_mode],[case_sensitivity])
- text — the cell or string to extract from;
- pattern — the regular expression in quotes;
- return_mode — optional: 0 (default) — first match, 1 — all matches as an array, 2 — capture groups from the first match;
- case_sensitivity — optional: 0 (default) — case-sensitive, 1 — case-insensitive.
Patterns use the PCRE2 syntax, the same as in REGEXTEST and REGEXREPLACE. Tokens like \d and \s, quantifiers and groups are explained in the article on regular expressions in Excel.
Three return modes
The mode determines how many values the formula returns. In modes 1 and 2 the result is an array that spills into the neighboring cells, so keep the cells to the right empty.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Text | Mode | Result | ||||
| 2 | Order 1024 placed 10/05/2026, total 3500 | 0 — first | 1024=REGEXEXTRACT(A2,"\d+") | ||||
| 3 | Order 1024 placed 10/05/2026, total 3500 | 1 — all | 1024=REGEXEXTRACT(A3,"\d+",1) | 10 | 05 | 2026 | 3500 |
| 4 | Order 1024 placed 10/05/2026, total 3500 | 2 — groups | 10=REGEXEXTRACT(A4,"(\d{2})/(\d{2})/(\d{4})",2) | 05 | 2026 |
Mode 1 returns all numbers: 1024, 10, 05, 2026 and 3500 — the array spills to the right. Mode 2 splits the first date into month, day and year — one cell for each pair of parentheses.
Extract a number, email or phone from text
The most common tasks are pulling a number, a contact or a code out of a string. REGEXEXTRACT always returns text: put two minus signs before the formula to calculate with the number.
| A | B | C | |
|---|---|---|---|
| 1 | Text | What we extract | Result |
| 2 | Weight 2.5 lb, price $1,290 | decimal number | 2.5=--REGEXEXTRACT(A2,"\d+(?:\.\d+)?") |
| 3 | Write to sales@shop.com or call | sales@shop.com=REGEXEXTRACT(A3,"[\w.+-]+@[\w-]+\.\w+") | |
| 4 | Tel. (206) 555-0142, Anna | phone | (206) 555-0142=REGEXEXTRACT(A4,"\(\d{3}\) \d{3}-\d{4}") |
| 5 | SKU: AB-20391, in stock 4 | SKU by mask | AB-20391=REGEXEXTRACT(A5,"[A-Z]{2}-\d+") |
In the first row the double minus turns the text “2.5” into the number 2.5. The formula returns the first number found; the price “1,290” with a thousands separator would need a pattern like [\d,]+.
Text after a label: lookbehind
Often you need the value after a label: “Invoice #48213”, “code: 4521”. PCRE2 supports lookbehind (?<=…): it requires the label to be in front of the fragment but leaves it out of the result. Lookahead (?=…) does the same for text after the fragment. Another way is mode 2 with a group.
| A | B | C | |
|---|---|---|---|
| 1 | Text | Pattern | Result |
| 2 | Supplier Acme Inc, Invoice #48213 | (?<=Invoice #)\d+ | 48213=REGEXEXTRACT(A2,"(?<=Invoice #)\d+") |
| 3 | Amount due: 15,400 USD | [\d,]+(?= USD) | 15,400=REGEXEXTRACT(A3,"[\d,]+(?= USD)") |
| 4 | Contract number: C-118/26 dated 03/01 | Contract number: (\S+) | C-118/26=REGEXEXTRACT(A4,"Contract number: (\S+)",2) |
(?<=Invoice #) means “preceded by Invoice #”. [\d,]+(?= USD) — digits and commas followed by “ USD”. In the third row the group (\S+) in mode 2 returns just the number without the label.
All matches in one cell
Mode 1 spills the matches into separate cells. To collect them in one cell separated by commas, wrap the formula in TEXTJOIN; to count them — in COUNTA.
| A | B | C | |
|---|---|---|---|
| 1 | Text | All hashtags | Count |
| 2 | Best #deals and #sales of the week #excel | #deals, #sales, #excel=IFERROR(TEXTJOIN(", ",TRUE,REGEXEXTRACT(A2,"#[^\s#]+",1)),"") | 3=IFERROR(COUNTA(REGEXEXTRACT(A2,"#[^\s#]+",1)),0) |
| 3 | no tags | =IFERROR(TEXTJOIN(", ",TRUE,REGEXEXTRACT(A3,"#[^\s#]+",1)),"") | 0=IFERROR(COUNTA(REGEXEXTRACT(A3,"#[^\s#]+",1)),0) |
The second row has no tags: REGEXEXTRACT returned #N/A, and IFERROR replaced it with an empty string and 0.
Case-insensitive extraction
By default “Excel” and “excel” are different strings. The fourth argument 1 turns off case sensitivity, and the result is returned as written in the text. The (?i) modifier at the start of the pattern does the same.
| A | B | C | |
|---|---|---|---|
| 1 | Text | Formula | Result |
| 2 | Download the EXCEL template for free | case-sensitive | #N/A=REGEXEXTRACT(A2,"excel") |
| 3 | Download the EXCEL template for free | case-insensitive, argument 1 | EXCEL=REGEXEXTRACT(A3,"excel",0,1) |
| 4 | Download the EXCEL template for free | case-insensitive, (?i) | EXCEL=REGEXEXTRACT(A4,"(?i)excel") |
The match is returned in its original spelling — “EXCEL”. To change its case, wrap the result in LOWER or UPPER.
If your Excel doesn’t have REGEXEXTRACT
In Excel 2010–2024 without a Microsoft 365 subscription, the free !SEMTools add-in adds its own REGEXEXTRACT function: =REGEXEXTRACT(A2,"\d+"). In Excel for Microsoft 365 the built-in function with the same name takes priority. Differences of the add-in version:
- if there’s no match, it returns an empty string instead of #N/A;
- it always returns the whole match — groups in parentheses aren’t extracted separately; to get a group, use REGEXREPLACE with $1: =REGEXREPLACE(A2,".*Invoice #(\d+).*","$1");
- a third argument TRUE returns all matches in one cell, the fourth sets the separator: =REGEXEXTRACT(A2,"\d+",TRUE,", ");
- the VBScript engine doesn’t support lookbehind (?<=…).
Why REGEXEXTRACT returns an error: common errors
- #N/A. No match was found. Check the pattern or wrap the formula in IFERROR.
- #SPILL!. In modes 1 and 2 the array has nowhere to spill — clear the cells to the right.
- The number doesn’t add up. REGEXEXTRACT returns text — add -- or VALUE.
- Too much is extracted. The * and + quantifiers are greedy: .* captures text up to the last possible match. Use the lazy .*? or a narrower character class.
- #NAME?. The function isn’t available in your Excel version — install !SEMTools to get REGEXEXTRACT.
REGEXEXTRACT function — FAQ
What does the REGEXEXTRACT function do in Excel?
It extracts a fragment of text by a regular expression: =REGEXEXTRACT(A2,"\d+") returns the first number.
How do I extract all numbers from a cell?
Use mode 1: =REGEXEXTRACT(A2,"\d+",1) — the numbers spill into separate cells; TEXTJOIN collects them in one.
How do I extract only part of a match?
Put that part in parentheses and use mode 2: =REGEXEXTRACT(A2,"Invoice #(\d+)",2).
Why does REGEXEXTRACT return #N/A?
The text has no fragment matching the pattern. To get an empty cell instead of the error, use =IFERROR(REGEXEXTRACT(A2,"\d+"),"").
What is REGEXEXTRACT called in Russian Excel?
РЕГИЗВЛЕЧЬ.
Extract numbers, words, emails and any fragments by pattern from a whole column at once — without formulas, in a couple of clicks with !SEMTools, in any Excel version.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary: the REGEXEXTRACT function in Excel
| Task | Formula |
|---|---|
| First number | =REGEXEXTRACT(A2,"\d+") |
| All numbers | =REGEXEXTRACT(A2,"\d+",1) |
| Part in parentheses | =REGEXEXTRACT(A2,"Invoice #(\d+)",2) |
| After a label | =REGEXEXTRACT(A2,"(?<=Invoice #)\d+") |
| No #N/A error | =IFERROR(REGEXEXTRACT(A2,"\d+"),"") |
I hope you found what you were looking for on this page. REGEXEXTRACT replaces long combinations of MID, SEARCH and LEN with a single formula: describe the shape of the fragment, and Excel finds it in the text.