REGEXEXTRACT
REGEXEXTRACT in Dutch Excel
Pull the part of a text that fits a pattern, such as an invoice number inside a bank description.
Last updated: 23 October 2026
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Date | Description | Amount | Invoice no. |
| 2 | 5 Jan 2026 | SEPA Overboeking Bakker Metaal BV INV-250871 dank u | €4,235.50€ 4.235,50 | INV-250871=REGEXEXTRACT(B2, "INV-[0-9]{6}")=REGEXEXTRACT(B2; "INV-[0-9]{6}") |
| 3 | 6 Jan 2026 | iDEAL Kroon Bouw betaling INV-250902 en INV-250903 | €12,100.25€ 12.100,25 | INV-250902=REGEXEXTRACT(B3, "INV-[0-9]{6}")=REGEXEXTRACT(B3; "INV-[0-9]{6}") |
| 4 | 7 Jan 2026 | Betaling Smit Handel factuur INV-251004 restant | €980.00€ 980,00 | INV-251004=REGEXEXTRACT(B4, "INV-[0-9]{6}")=REGEXEXTRACT(B4; "INV-[0-9]{6}") |
Swipe sideways to see all columns
Result: INV-250871
=REGEXEXTRACT(B2,"INV-[0-9]{6}")
=REGEXEXTRACT(B2;"INV-[0-9]{6}")
What REGEXEXTRACT does and when to use it
What it does: it returns the part of a text that fits a pattern, for example the invoice number inside a bank description. The pattern is a regular expression. REGEXEXTRACT, REGEXTEST and REGEXREPLACE are Microsoft 365 functions, so older Excel versions show #NAME?.
What it replaces: chains of MID, FIND and LEN that stop working as soon as the text in front of the number changes length.
Typical uses
Situations that call for it:
- a reference sits somewhere inside free text, such as a bank description or the memo of a journal line
- a code has a fixed shape (letters, then digits) but a varying position
- one text holds several references and you want all of them
When the text is split cleanly by one delimiter, TEXTBEFORE, TEXTAFTER or TEXTSPLIT are shorter and need no pattern.
REGEXEXTRACT or MID and FIND?
Compare with MID
- MID and FIND: you count the characters
=MID(B2,
FIND("INV-", B2), 10) =MID(B2;
FIND("INV-"; B2); 10) MID DEELCharacters from the middle of a text, by start position and length.Read more about MIDPress Escape to closetextThe text to take a piece from; usually a cell reference.Press Escape to closeFIND VIND.ALLESThe position of a text inside another, case-sensitive.Read more about FINDPress Escape to closefind_textThe text to look for. Case counts; * and ? are ordinary characters.Press Escape to closewithin_textThe text to search in; usually a cell reference.Press Escape to closenum_charsHow many characters to take. More than are left is allowed.Press Escape to close - REGEXEXTRACT: you describe the shape
=REGEXEXTRACT(B2,
"INV-[0-9]{6}") =REGEXEXTRACT(B2;
"INV-[0-9]{6}") REGEXEXTRACTReturns the part of a text that matches a regular expression.Press Escape to closetextThe text to search, usually a cell.Press Escape to closepatternThe regular expression that describes the part you want.Press Escape to close
What changes when you move from MID and FIND to a pattern
- the 10 (the length of the number) disappears: the pattern says six digits
- a description without INV- gives #VALUE! with MID and FIND and #N/A with REGEXEXTRACT, but only the pattern refuses INV- followed by too few digits
- the pattern can be a cell, so one formula serves several reference types
Colleague on Excel 2019 or older
Excel 2021 and 2024 have no REGEXEXTRACT and show #NAME?. For files that go to those versions, keep MID and FIND.
How Excel evaluates it
Excel tries the pattern at each position of the text, from the left. The first position where the whole pattern fits gives the first match.
The third argument, return_mode, sets what comes back:
- 0 (default): the first match, as text
- 1: every match, as an array that spills across the row
- 2: what each pair of parentheses in the pattern captured, for the first match only
When no position fits, the result is #N/A. The result is always text, also when it looks like a number.
Watch out: REGEXEXTRACT always returns text, and #N/A when nothing fits. Put VALUE around it for a number and IFNA around it for a message you can follow up.
Mental model
A highlighter on a printed page: the pattern describes what the highlighted part looks like, and REGEXEXTRACT copies only the highlighted text: the first piece, every piece, or the pieces inside brackets.
Terms used on this page: regular expression, reference, spill, array, argument
REGEXEXTRACT(text,pattern, [return_mode], [case_sensitivity])
REGEXEXTRACT(text;pattern; [return_mode]; [case_sensitivity])
| Argument | Type | Meaning | Default |
|---|---|---|---|
text | Required | The text to search, usually a cell. | |
pattern | Required | The regular expression that describes the part you want. | |
return_mode | Optional | 0 first match, 1 all matches, 2 capture groups of the first match. | 0 |
case_sensitivity | Optional | 0 case sensitive (default), 1 ignore case. | 0 |
Return value:The first match as text, all matches or the capture groups as an array, or #N/A when nothing fits.
Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).
Worked examples
Beginner
Pull an invoice number out of a bank description
You are matching the January receipts on the client's bank statement to open invoices. Every description carries the invoice number as INV- followed by six digits, but the words around it differ per payer, so a fixed position does not work.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Date | Description | Amount | Invoice no. | |
| 2 | 5 Jan 2026 | SEPA Overboeking Bakker Metaal BV INV-250871 dank u | €4,235.50€ 4.235,50 | INV-250871=REGEXEXTRACT(B2, "INV-[0-9]{6}")=REGEXEXTRACT(B2; "INV-[0-9]{6}") | |
| 3 | 6 Jan 2026 | iDEAL Kroon Bouw betaling INV-250902 en INV-250903 | €12,100.25€ 12.100,25 | INV-250902=REGEXEXTRACT(B3, "INV-[0-9]{6}")=REGEXEXTRACT(B3; "INV-[0-9]{6}") | |
| 4 | 7 Jan 2026 | Betaling Smit Handel factuur INV-251004 restant | €980.00€ 980,00 | INV-251004=REGEXEXTRACT(B4, "INV-[0-9]{6}")=REGEXEXTRACT(B4; "INV-[0-9]{6}") | |
| 5 |
Swipe sideways to see all columns
Formula in D2
=REGEXEXTRACT(B2,"INV-[0-9]{6}")
=REGEXEXTRACT(B2;"INV-[0-9]{6}")
Result: INV-250871
Step by step
B2holds the description “SEPA Overboeking Bakker Metaal BV INV-250871 dank u”. The pattern isINV-[0-9]{6}: the characters INV- exactly as typed, then[0-9]{6}, which means one digit from 0 to 9, six times in a row.- Excel tries the pattern at every position of the text, from the left. At “SEPA”, “Overboeking”, “Bakker”, “Metaal” and “BV” it fails at once, because none of these positions starts with INV-.
- At the position of “INV-250871” the four characters INV- fit, and six digits follow: 2, 5, 0, 8, 7 and 1. The whole pattern fits, so this is the first match.
- REGEXEXTRACT returns only the matching part, as text: INV-250871 in
D2. The words before and after the number are dropped. - Copy
D2down.B3holds two numbers, INV-250902 and INV-250903, and the formula returns only the first one, INV-250902.D4returns INV-251004.
Watch out: The default is case sensitive: a description with inv-250902 returns #N/A, because the pattern asks for capitals. The first variation fixes that.
More on this example: why it works, 1 more pitfall, 3 variations
Why it works
A regular expression describes the shape of what you want, not its position, so the formula keeps working when the words around the number change length. The count {6} is what separates an invoice number from any other run of digits.
More to watch out for
- The pattern does not check what follows the six digits. A description with
INV-2508711(seven digits) silently returns INV-250871, a number that does not exist. Test the extracted numbers against your invoice list.
Variations
=REGEXEXTRACT(B2,"INV-[0-9]{6}", 0, 1)
=REGEXEXTRACT(B2;"INV-[0-9]{6}"; 0; 1)
The fourth argument 1 ignores case, so inv-250902 is found too. The third argument must be filled in to reach it: 0 is the first match.
=REGEXEXTRACT(B2,"(?i)INV-[0-9]{6}")
=REGEXEXTRACT(B2;"(?i)INV-[0-9]{6}")
(?i) at the start of the pattern ignores case as well, and travels with the pattern when it sits in a cell.
=TEXTJOIN(", ",TRUE, REGEXEXTRACT(B2, "INV-[0-9]{6}", 1))
=TEXTJOIN(", ";TRUE; REGEXEXTRACT(B2; "INV-[0-9]{6}"; 1))
A customer who pays several invoices at once lists them all, as in row 3. With the third argument 1 REGEXEXTRACT returns every match (INV-250902 and INV-250903) as a spill; TEXTJOIN puts them in one cell, INV-250902, INV-250903, ready to split against the receivables list. Rows with one invoice stay as they are.
Intermediate
Read an asset number from a description as a number
You test additions to fixed assets. Each purchase invoice line ends with an asset tag, AS- and five digits, and the fixed asset register (activastaat) stores those five digits as a number. The description also holds a model number, so the first number in the text is the wrong one.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Entry no. | Description | Amount | Asset no. | |
| 2 | 25-0611 | Laptop Dell Latitude 5440 AS-10458 | €1,189.00€ 1.189,00 | 1045810458=VALUE(REGEXEXTRACT(B2, "[0-9]{5}$"))=VALUE(REGEXEXTRACT(B2; "[0-9]{5}$")) | |
| 3 | 25-0634 | Bureaustoel Ahrend Axia AS-10471 | €645.00€ 645,00 | 1047110471=VALUE(REGEXEXTRACT(B3, "[0-9]{5}$"))=VALUE(REGEXEXTRACT(B3; "[0-9]{5}$")) | |
| 4 | 25-0652 | Heftruck Linde E16 AS-10503 | €18,250.00€ 18.250,00 | 1050310503=VALUE(REGEXEXTRACT(B4, "[0-9]{5}$"))=VALUE(REGEXEXTRACT(B4; "[0-9]{5}$")) | |
| 5 | 25-0689 | Printer HP LaserJet M428 AS-10512 | €389.00€ 389,00 | 1051210512=VALUE(REGEXEXTRACT(B5, "[0-9]{5}$"))=VALUE(REGEXEXTRACT(B5; "[0-9]{5}$")) | |
| 6 |
Swipe sideways to see all columns
Formula in D2
=VALUE(REGEXEXTRACT(B2,"[0-9]{5}$"))
=VALUE(REGEXEXTRACT(B2;"[0-9]{5}$"))
Result: 1045810458
Step by step
B2holds the description “Laptop Dell Latitude 5440 AS-10458”. The inner function runs first: REGEXEXTRACT with the pattern[0-9]{5}$, which asks for five digits at the very end of the text ($stands for the end).- Excel tries the pattern from the left. The model number 5440 has four digits and is followed by a space, so it fails. At 10458 five digits are followed by the end of the text, so the pattern fits.
- REGEXEXTRACT returns the text “10458”. It looks like a number, but it is text, and Excel treats it that way.
- VALUE turns that text into the number 10458 in
D2. The text holds digits only, so the regional setting plays no part. - Copy
D2down:D3returns 10471,D4returns 10503 andD5returns 10512. - Without VALUE,
D2looks the same but holds text. Looking up the number 10458 in a register of numbers then finds nothing (#N/A), and SUM overD2:D5gives 0.
Watch out: Write the pattern as [0-9]+ and D2 returns 5440, the model number, with no error. The formula looks fine and every row gets a plausible number. Check one row by eye.
More on this example: why it works, 1 more pitfall, 2 variations
Why it works
REGEXEXTRACT always returns text, whatever the pattern matched; VALUE is the step that makes it a number, and it goes around the function. The anchor $ states where the asset tag sits, so a number earlier in the text cannot be picked up.
More to watch out for
- A line without an asset tag, such as a service invoice, returns #N/A. The first variation shows a message instead.
Variations
=IFNA(VALUE(REGEXEXTRACT(B2,"[0-9]{5}$")), "No asset no.")
=IFNA(VALUE(REGEXEXTRACT(B2;"[0-9]{5}$")); "No asset no.")
IFNA catches only #N/A, so lines without an asset tag show “No asset no.” while a real problem stays visible.
=--REGEXEXTRACT(B2,"[0-9]{5}$")
=--REGEXEXTRACT(B2;"[0-9]{5}$")
The double minus converts the text to a number as VALUE does. Prefer VALUE when others will read the formula: its name says what it does.
Advanced
Split a journal key into its four parts
A client's export holds one key per journal line, built as journal, entry no., relation and VAT percentage, separated by pipes. You want the four parts in four cells to test the VAT, and keys with a different build must be flagged, not cut up wrongly.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Journal key | Amount excl. VAT | Journal | Entry no. | Relation | VAT % | |
| 2 | VRK|25-0388|D1002|21% | €1,850.00€ 1.850,00 | VRK=IFNA(REGEXEXTRACT(A2, "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([A-Z][0-9]{4})\|([0-9]+)%$", 2), "Check key")=IFNA(REGEXEXTRACT(A2; "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([A-Z][0-9]{4})\|([0-9]+)%$"; 2); "Check key") | 25-0388 | D1002 | 21 | |
| 3 | VRK|25-0391|D1007|9% | €420.00€ 420,00 | |||||
| 4 | INK|25-0145|C2001|21% | €3,000.00€ 3.000,00 | |||||
| 5 | MEM|25-0912|D1002|0% | €275.00€ 275,00 | |||||
| 6 | VRK/25-0399/D1004/21% | €960.00€ 960,00 | |||||
| 7 |
Swipe sideways to see all columns
Formula in C2
=IFNA(REGEXEXTRACT(A2,"^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([A-Z][0-9]{4})\|([0-9]+)%$", 2), "Check key")
=IFNA(REGEXEXTRACT(A2;"^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([A-Z][0-9]{4})\|([0-9]+)%$"; 2); "Check key")
Result: VRK | 25-0388 | D1002 | 21VRK | 25-0388 | D1002 | 21
Step by step
- The inner function runs first. REGEXEXTRACT reads the key in
A2, VRK|25-0388|D1002|21%, with a pattern of four groups:([A-Z]{3})three capitals,([0-9]{2}-[0-9]{4})an entry number,([A-Z][0-9]{4})a letter and four digits, and([0-9]+)one or more digits. - Between the groups the pattern asks for
\|, a real pipe. Without the backslash|would mean “or”.^and$pin the pattern to the start and the end, and%sits outside the last group. - The whole key fits: VRK, then 25-0388, then D1002, then 21 followed by %. With return_mode 2 Excel returns what the four groups captured, not the whole match: VRK, 25-0388, D1002 and 21.
- IFNA looks at the result: no #N/A, so it passes the four values on. They spill into
C2:F2. The % is not in the fourth value, and all four are text. - Copy the formula to row 3 and the same happens for VRK|25-0391|D1007|9%. For
A6, VRK/25-0399/D1004/21%, no position fits because the key uses slashes: REGEXEXTRACT gives #N/A, and IFNA shows “Check key” inC6only.
Watch out: Forget the backslash and | means “or”. The pattern then finds VRK alone, so there is a match, IFNA does not step in, and the other cells do not hold the parts you wanted. No error shows.
More on this example: why it works, 2 more pitfalls, 2 variations
Why it works
Each pair of parentheses defines a group, and return_mode 2 hands back the groups instead of the whole match. Because the pattern starts with ^ and ends with $, it describes the whole key: a key with an extra part or another delimiter is rejected with #N/A instead of being cut up wrongly.
More to watch out for
- If one of
D2:F2is not empty, the result is #SPILL! (#OVERLOPEN!) instead of the four values. - The fourth value is the text “21”, not a number. A multiplication converts it, but a lookup or a SUM does not. Use VALUE, as in the first variation.
Variations
=B2 * VALUE(INDEX(REGEXEXTRACT(A2,"^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([A-Z][0-9]{4})\|([0-9]+)%$", 2), 4)) / 100
=B2 * VALUE(INDEX(REGEXEXTRACT(A2;"^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([A-Z][0-9]{4})\|([0-9]+)%$"; 2); 4)) / 100
Recalculates the VAT for the first key in one cell: INDEX takes the fourth group, VALUE makes the text 21 a number, and 1,850.001.850,00 × 21 / 100 returns 388.50.
=TEXTSPLIT(A2,"|")
=TEXTSPLIT(A2;"|")
When the key is plain text with one delimiter, TEXTSPLIT returns the four parts without a pattern (the last one as 21%, with the % sign). It does not check the shape, so a malformed key is cut up without any warning.
Master
Total every invoice a credit note memo mentions
A credit note memo names the invoices it corrects, typed by hand: the case varies and the number of invoices differs per memo. You want the open amount of all invoices it mentions in one cell, to compare with the credit note total.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Invoice no. | Customer | Open amount | |||
| 2 | F-25-1204 | Dijkstra Techniek BV | €4,235.50€ 4.235,50 | Memo | Creditnota Dijkstra Techniek: korting op f-25-1204, F-25-1211 en F-25-1219 (levering te laat) | |
| 3 | F-25-1207 | Van Rijn Logistiek BV | €1,980.00€ 1.980,00 | Credited | €25,085.75€ 25.085,75=LET( memo, E2, refs, IFNA(REGEXEXTRACT(memo, "F-[0-9]{2}-[0-9]{4}", 1, 1), "-"), SUM(SUMIFS(C2:C7, A2:A7, refs)) )=LET( memo; E2; refs; IFNA(REGEXEXTRACT(memo; "F-[0-9]{2}-[0-9]{4}"; 1; 1); "-"); SUM(SUMIFS(C2:C7; A2:A7; refs)) ) | |
| 4 | F-25-1211 | Dijkstra Techniek BV | €12,100.25€ 12.100,25 | |||
| 5 | F-25-1215 | Meijer Staalbouw BV | €760.00€ 760,00 | |||
| 6 | F-25-1219 | Dijkstra Techniek BV | €8,750.00€ 8.750,00 | |||
| 7 | F-25-1223 | Smit Verpakking BV | €3,025.00€ 3.025,00 | |||
| 8 |
Swipe sideways to see all columns
Formula in E3
=LET( memo,E2, refs, IFNA(REGEXEXTRACT(memo, "F-[0-9]{2}-[0-9]{4}", 1, 1), "-"), SUM(SUMIFS(C2:C7, A2:A7, refs)) )
=LET( memo;E2; refs; IFNA(REGEXEXTRACT(memo; "F-[0-9]{2}-[0-9]{4}"; 1; 1); "-"); SUM(SUMIFS(C2:C7; A2:A7; refs)) )
Result: €25,085.75€ 25.085,75
Step by step
- LET binds the name
memoto the text inE2and the namerefsto the next expression. Nothing is calculated yet. - For
refs, REGEXEXTRACT scans the memo forF-[0-9]{2}-[0-9]{4}: F-, two digits, a hyphen, four digits. The third argument 1 asks for all matches, the fourth argument 1 ignores case. - Three places fit: f-25-1204 (lowercase, found only because case is ignored), F-25-1211 and F-25-1219. The result is an array of three texts across one row.
- IFNA sees no #N/A and passes the array on. SUMIFS now runs once per reference, because the criteria are an array: the open amounts of F-25-1204, F-25-1211 and F-25-1219 are 4,235.504.235,50, 12,100.2512.100,25 and 8,750.008.750,00. SUMIFS ignores case, so f-25-1204 matches F-25-1204.
- SUM adds the three amounts: 4,235.504.235,50 + 12,100.2512.100,25 + 8,750.008.750,00 = 25,085.7525.085,75.
- Change
E2to a memo without an invoice number and REGEXEXTRACT gives #N/A. IFNA replaces it with “-”, which matches no invoice number, so the total is 0.
Watch out: Set case_sensitivity to 0, the default, and f-25-1204 is not found. The total becomes 20,850.2520.850,25 instead of 25,085.7525.085,75, with no error: a silent shortfall of 4,235.504.235,50.
More on this example: why it works, 1 more pitfall, 1 variation
Why it works
return_mode 1 turns the memo into a list of references, and SUMIFS with an array of criteria turns that list into a list of amounts, so no helper columns are needed. LET evaluates the pattern once and names it.
When not to use it: when the references are always split by the same delimiter, TEXTSPLIT replaces REGEXEXTRACT with no pattern to maintain, but it breaks when people type “, ” in one memo and “ en ” in the next. When the credited invoices can be entered in separate rows, plain SUMIFS over those rows is clearer and works in every version.
More to watch out for
- Leave return_mode at 0 and only the first match comes back. With case_sensitivity still 1 that is f-25-1204, so the total is 4,235.504.235,50 instead of 25,085.7525.085,75, again with no error.
Variations
=TEXTJOIN(", ",TRUE, REGEXEXTRACT(E2, "F-[0-9]{2}-[0-9]{4}", 1, 1))
=TEXTJOIN(", ";TRUE; REGEXEXTRACT(E2; "F-[0-9]{2}-[0-9]{4}"; 1; 1))
Shows the references that were found in one cell, as f-25-1204, F-25-1211, F-25-1219, so you can read what the total is built from.
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| #N/A on some rows | The text holds no part that fits the pattern in full: the case differs (inv- against INV-), a digit is missing, or the reference has a space where the pattern has a hyphen. | Lay the failing text next to the pattern and compare character by character. Set case_sensitivity to 1 for capitals, and put IFNA around the formula so rows without a match show a message you can follow up. |
| The number will not add up or find its match | The result is text, even when it is all digits. SUM skips it, and a lookup in a column of numbers does not find it. | Put VALUE around REGEXEXTRACT. VALUE reads plain digits the same way in every regional setting; for a part with a decimal separator, such as 12,50, it follows the regional setting, so test it on the computer that will use the file. |
| A wrong part comes back and no error shows | The pattern is looser than the text: [0-9]+ takes the first run of digits, which may be a model number or a date instead of the reference. | Describe what surrounds the reference: a fixed prefix, an exact count such as {6}, or an anchor such as $ for the end of the text. Test on rows with a near miss. |
| #SPILL! instead of the result | return_mode 1 or 2 returns several values, and a cell it needs next to the formula is not empty. | Clear the cells to the right of the formula, or return a single value with return_mode 0. |
| #NAME? in Excel 2021 or 2024 | The workbook is opened in a version without the regex functions, which exist only in Microsoft 365. | Keep the formula for Microsoft 365 users and give everyone else a version with MID and FIND, which work in every version. |
Used in combinations
- Find the customer with a regex lookupPull the invoice number out of a bank description with REGEXEXTRACT, turn it into a pattern, and let XLOOKUP match that pattern against an invoice list that is typed in different ways.Excel for Microsoft 365 (This combination needs Excel for Microsoft 365)