REGEXEXTRACT Function in Excel
Quick answer
REGEXEXTRACT pulls text out of a cell using a regular expression pattern. Use it when the bit you want (an invoice number, an amount, a postcode) sits in a different spot in every cell, so LEFT, MID and TEXTBEFORE can’t find it. It returns the first match by default, or every match with return_mode 1.
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])Works in: Excel 365 (Windows and Mac) and Excel for the web only. It is not in Excel 2024, 2021, 2019 or 2016. Google Sheets has its own REGEXEXTRACT, but it only takes two arguments and works a little differently (see the FAQ).
Syntax
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])| Argument | Required? | What it does |
|---|---|---|
text | Yes | The text or cell you want to pull something out of. |
pattern | Yes | The regular expression describing what to find, in double quotes. Excel uses the PCRE2 flavour, so \d means any digit and + means one or more. |
return_mode | No | What to return: 0 the first match, 1 every match as a spilled array, 2 the capturing groups (the parts of the pattern in brackets) from the first match. Default: 0. |
case_sensitivity | No | 0 matches case exactly, 1 ignores case. Default: 0 (case-sensitive). |
How REGEXEXTRACT works
Think of the pattern as a description of the text you’re after rather than its position. INV-\d+ reads as “the letters INV, a hyphen, then one or more digits”. Excel scans the cell from left to right and hands back the first chunk that fits the description, wherever it sits.
That’s the big difference from LEFT, MID and FIND. Those need the text to be in the same place, or after the same delimiter, in every row. Bank feeds, supplier emails and exported notes are rarely that tidy, and REGEXEXTRACT doesn’t care.
Two things to keep in mind. The result is always text, even when it looks like a number, so wrap it in VALUE before you add it up. And matching is case-sensitive unless you set the last argument to 1, which is the opposite of most Excel lookup functions.
Examples
All three examples use the Invoices sheet from the Bluegum Café workbook, with a third supplier bill added in row 4 and a new column H holding the description exactly as it came through on the bank feed.
| Row | A: Invoice No | B: Date | C: Supplier | D: Category | E: Amount ex GST | F: GST | G: Total | H: Bank Feed Note |
|---|---|---|---|---|---|---|---|---|
| 2 | INV-1001 | 03/07/2025 | Ridgeline Roasters | Coffee beans | $480.00 | $48.00 | $528.00 | EFT 03/07 Ridgeline Roasters INV-1001 $528.00 |
| 3 | INV-1002 | 04/07/2025 | Coastal Dairy Co | Milk | $215.50 | $21.55 | $237.05 | coastal dairy co inv-1002 $237.05 direct debit |
| 4 | INV-1003 | 07/07/2025 | GreenLeaf Packaging | Takeaway cups | $1,150.00 | $115.00 | $1,265.00 | Transfer to GreenLeaf Packaging ref INV-1003 amt $1,265.00 |
Basic: Pull the invoice number out of a bank feed note
On the Invoices sheet, the note is in H2. Enter this formula in I2:
=REGEXEXTRACT(H2,"INV-\d+")With EFT 03/07 Ridgeline Roasters INV-1001 $528.00 in H2, the result is INV-1001. The same formula returns INV-1003 in row 4, even though the reference sits in a different spot in that note.
Real-world: Pull the paid amount out to reconcile against your bills
Before BAS time I want to check that what left the bank matches what the supplier billed. On the Invoices sheet, the note is in H4. Enter this formula in J4 to pull the dollar amount out as a real number:
=VALUE(REGEXEXTRACT(H4,"[\d,]+\.\d{2}"))The pattern means “digits and commas, then a full stop, then exactly two digits”. With the GreenLeaf Packaging note in H4, REGEXEXTRACT returns the text 1,265.00 and VALUE turns it into 1265. Format J4 as currency and it shows $1,265.00, and =J4=G4 returns TRUE. Fill J2:J4 and any row showing FALSE is one to chase up.
Advanced: Grab the invoice number and amount in one go with capturing groups
On the Invoices sheet, the note is in H4. Enter this formula in L4 and it spills into L4:M4:
=REGEXEXTRACT(H4,"(INV-\d+).*\$([\d,]+\.\d{2})",2)Each set of brackets is a capturing group, and return_mode 2 returns one cell per group. The result is INV-1003 in L4 and 1,265.00 in M4. The .* in the middle skips whatever sits between the two, and \$ is escaped because a bare $ means “end of text” in regex.
Guru Tip
Watch out for case. Point the basic formula at H3 and you get #N/A, because the Coastal Dairy note says inv-1002 in lowercase and REGEXEXTRACT is case-sensitive out of the box. Bank feeds are all over the shop with capitals, so I add a 1 as the fourth argument on anything that comes from a bank or an email: =REGEXEXTRACT(H3,"INV-\d+",0,1) returns inv-1002. Wrap it in UPPER if you need it to match your invoice list.
Common errors
#N/A: no match found
Cause: Nothing in the text fits the pattern. Most often it’s a case mismatch, a typo in the pattern, or a row that simply doesn’t contain the thing you’re looking for. Fix: Set case_sensitivity to 1, test the pattern on one cell you know should match, and wrap the formula in IFERROR (for example =IFERROR(REGEXEXTRACT(H2,"INV-\d+"),"No ref")) for rows that legitimately have no match.
#VALUE!: the pattern isn’t valid
Cause: The pattern breaks regex rules, such as an unclosed bracket in "(INV-\d+" or a stray * at the start. Fix: Check every ( and [ has a partner, and put a backslash before characters you mean literally, like \$, \. and \(.
#SPILL!: something is in the way
Cause: With return_mode 1 or 2 the result spills into neighbouring cells, and one of them isn’t empty. Fix: Clear the cells to the right of the formula, or move it somewhere with room.
#NAME?: your Excel doesn’t have the function
Cause: You’re on Excel 2024 or earlier, which don’t include the regex functions. Fix: Use Excel 365 or Excel for the web, or fall back to TEXTBEFORE, TEXTAFTER or MID with SEARCH if the text has a consistent structure.
Grab the template: REGEXEXTRACT
Paste in your bank feed and have the invoice numbers and amounts pulled out and checked against your bills for you.