REGEXREPLACE
REGEXREPLACE in Dutch Excel
Replace the parts of a text that match a pattern, and reuse pieces of the match to reorder or reformat what stays.
Last updated: 23 October 2026
| Row | A | B | C |
|---|---|---|---|
| 1 | Supplier | VAT number as typed | Cleaned |
| 2 | Hoogland Staalbouw BV | NL 8123.45.678.B01 | NL812345678B01=REGEXREPLACE(B2, "[^A-Za-z0-9]", "")=REGEXREPLACE(B2; "[^A-Za-z0-9]"; "") |
| 3 | Rijnmond Software BV | NL-8567-89-012-B01 | NL856789012B01=REGEXREPLACE(B3, "[^A-Za-z0-9]", "")=REGEXREPLACE(B3; "[^A-Za-z0-9]"; "") |
| 4 | Delta Verpakking BV | NL 8456 78901 B02 | NL845678901B02=REGEXREPLACE(B4, "[^A-Za-z0-9]", "")=REGEXREPLACE(B4; "[^A-Za-z0-9]"; "") |
| 5 | Kuiper Logistiek BV | nl.8234.56.789.b01 | nl823456789b01=REGEXREPLACE(B5, "[^A-Za-z0-9]", "")=REGEXREPLACE(B5; "[^A-Za-z0-9]"; "") |
Swipe sideways to see all columns
Result: NL812345678B01
=REGEXREPLACE(B2,"[^A-Za-z0-9]", "")
=REGEXREPLACE(B2;"[^A-Za-z0-9]"; "")
What REGEXREPLACE does and when to use it
REGEXREPLACE, REGEXTEST and REGEXEXTRACT are Microsoft 365 functions that read text through a regular expression, a pattern that describes text by its shape, such as two digits, a hyphen and four digits.
REGEXREPLACE swaps every part of a text that fits the pattern for new text. In the new text, $1 and $2 bring back the parts the pattern put between parentheses, so you can reorder and reformat a reference as well as delete from it.
Typical uses
Use it to normalize references before you compare or look them up: strip spaces and punctuation from VAT numbers, put a postcode in one shape, turn a journal key into an entry number. It also repairs text exports, such as dates written the wrong way round. When the text to replace is fixed, for example one dash, SUBSTITUTE does the same in every Excel version and reads more clearly.
SUBSTITUTE or REGEXREPLACE?
Compare with SUBSTITUTE
- SUBSTITUTE: one fixed character per call
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,
" ", ""), ".", ""), "-", "") =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2;
" "; ""); "."; ""); "-"; "") SUBSTITUTE SUBSTITUERENReplaces occurrences of one text with another.Read more about SUBSTITUTEPress Escape to closeSUBSTITUTE SUBSTITUERENReplaces occurrences of one text with another.Read more about SUBSTITUTEPress Escape to closeSUBSTITUTE SUBSTITUERENReplaces occurrences of one text with another.Read more about SUBSTITUTEPress Escape to closetextThe text or cell in which to replace something.Press Escape to closeold_textWhat to look for; case-sensitive, no wildcards.Press Escape to closenew_textWhat to put in its place; "" removes it.Press Escape to closeold_textWhat to look for; case-sensitive, no wildcards.Press Escape to closenew_textWhat to put in its place; "" removes it.Press Escape to closeold_textWhat to look for; case-sensitive, no wildcards.Press Escape to closenew_textWhat to put in its place; "" removes it.Press Escape to close - REGEXREPLACE: one pattern for all
=REGEXREPLACE(B2,
"[^A-Za-z0-9]", "") =REGEXREPLACE(B2;
"[^A-Za-z0-9]"; "") REGEXREPLACEReplaces the parts of a text that match a regular expression.Press Escape to closetextThe text to search in.Press Escape to closepatternThe regular expression that marks what to replace.Press Escape to closereplacementThe new text; $1, $2 insert parts of the match.Press Escape to close
What changes between the two
- SUBSTITUTE needs one nested call for each character (space, dot, hyphen) and misses the next one you meet, such as a slash.
- REGEXREPLACE says what to keep instead of what to remove, so a new separator needs no new call.
- SUBSTITUTE cannot reorder parts of a text; REGEXREPLACE can with
$1and$2. - SUBSTITUTE works in every Excel version; REGEXREPLACE needs Microsoft 365.
Colleague on Excel 2019 or older
Excel 2021, Excel 2024 and older versions do not have REGEXREPLACE and show #NAME? once the file recalculates. For files that go to those versions, keep SUBSTITUTE for fixed texts.
How Excel evaluates it
Excel reads the text and the pattern, then scans the text from left to right and marks every stretch that fits the pattern. A match ends before the next search starts, so matches never overlap. Each marked stretch is swapped for the replacement; in the replacement, $1 is the text captured by the first pair of parentheses in the pattern, $2 the second pair, and so on. Text outside the matches stays as it is. With occurrence 0 (the default) every match is swapped; a positive number swaps only that match counted from the left, and a negative number only that match counted from the right. When nothing fits, the text comes back unchanged, and the result is always text.
Watch out: REGEXREPLACE never reports a pattern that finds nothing: the text comes back unchanged. A result that looks the same as the input is the signal to test the pattern with REGEXTEST.
Terms used on this page: regular expression, reference
REGEXREPLACE(text,pattern, replacement, [occurrence], [case_sensitivity])
REGEXREPLACE(text;pattern; replacement; [occurrence]; [case_sensitivity])
| Argument | Type | Meaning | Default |
|---|---|---|---|
text | Required | The text to search in. | |
pattern | Required | The regular expression that marks what to replace. | |
replacement | Required | The new text; $1, $2 insert parts of the match. | |
occurrence | Optional | Which match to replace: 0 = all (default), n = the nth, negative = from the end. | 0 |
case_sensitivity | Optional | 0 = case sensitive (default), 1 = ignore case. | 0 |
Return value:The text with the matching parts replaced, or the original text when nothing matches.
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
Strip spaces and dots from VAT numbers
The creditor file you received from the client holds VAT numbers typed in four different ways. Before you compare them with the numbers on the invoices, you want each one as a single run of letters and digits.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Supplier | VAT number as typed | Cleaned | |
| 2 | Hoogland Staalbouw BV | NL 8123.45.678.B01 | NL812345678B01=REGEXREPLACE(B2, "[^A-Za-z0-9]", "")=REGEXREPLACE(B2; "[^A-Za-z0-9]"; "") | |
| 3 | Rijnmond Software BV | NL-8567-89-012-B01 | NL856789012B01=REGEXREPLACE(B3, "[^A-Za-z0-9]", "")=REGEXREPLACE(B3; "[^A-Za-z0-9]"; "") | |
| 4 | Delta Verpakking BV | NL 8456 78901 B02 | NL845678901B02=REGEXREPLACE(B4, "[^A-Za-z0-9]", "")=REGEXREPLACE(B4; "[^A-Za-z0-9]"; "") | |
| 5 | Kuiper Logistiek BV | nl.8234.56.789.b01 | nl823456789b01=REGEXREPLACE(B5, "[^A-Za-z0-9]", "")=REGEXREPLACE(B5; "[^A-Za-z0-9]"; "") | |
| 6 |
Swipe sideways to see all columns
Formula in C2
=REGEXREPLACE(B2,"[^A-Za-z0-9]", "")
=REGEXREPLACE(B2;"[^A-Za-z0-9]"; "")
Result: NL812345678B01
Step by step
B2holdsNL 8123.45.678.B01, 18 characters. The pattern[^A-Za-z0-9]is a character class: the^right after the opening bracket means “any character that is not in this list”, and the list is every letter and every digit.- Excel scans the text from left to right.
N,Land every digit are in the list, so they do not match. The space afterNLand the three dots do not belong to the list: four matches. - The replacement is an empty text (
""), so each of the four matches is deleted. Occurrence is left out, which means 0: every match, not only the first. - 18 characters minus 4 deleted = 14 characters:
C2showsNL812345678B01. - Copy
C2down.NL-8567-89-012-B01loses 4 hyphens and givesNL856789012B01;NL 8456 78901 B02loses 3 spaces and givesNL845678901B02;nl.8234.56.789.b01loses 4 dots and givesnl823456789b01, still in lowercase.
Watch out: The letters keep their case: nl823456789b01 does not equal NL823456789B01 for a case sensitive pattern such as ^NL in REGEXTEST. A comparison with =, XLOOKUP or SUMIFS ignores case, so there it does no harm.
More on this example: why it works, 1 more pitfall, 2 variations
Why it works
A negated character class names what to keep, so you do not have to list every separator you might meet. The empty replacement turns each match into nothing, and the default occurrence makes the replacement apply to all of them.
More to watch out for
- Do not use this pattern on names: a letter such as
éis not inA-Z, so it would be deleted along with the spaces.
Variations
=REGEXREPLACE(B2,"[ .-]", "")
=REGEXREPLACE(B2;"[ .-]"; "")
Name the separators you expect (space, dot, hyphen) instead of everything else. A character you did not expect then stays visible in the result.
=UPPER(REGEXREPLACE(B2,"[^A-Za-z0-9]", ""))
=UPPER(REGEXREPLACE(B2;"[^A-Za-z0-9]"; ""))
Add UPPER when the next step compares case sensitively. The fourth supplier then gives NL823456789B01.
Intermediate
Put postcodes in one shape
You send address confirmations and match the client's customer list with a postcode table. The list has postcodes typed as 3011ab, 2406 be and 2801 mm , and one entry that is no postcode at all. You want four digits, one space and two letters, and the odd entry left alone for you to follow up.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Customer | Postcode as typed | Postcode | |
| 2 | Schouten Installatie BV | 3011ab | 3011 ab=REGEXREPLACE(B2, "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$", "$1 $2", 0, 1)=REGEXREPLACE(B2; "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$"; "$1 $2"; 0; 1) | |
| 3 | Smit Handel BV | 2406 be | 2406 be=REGEXREPLACE(B3, "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$", "$1 $2", 0, 1)=REGEXREPLACE(B3; "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$"; "$1 $2"; 0; 1) | |
| 4 | Prins Bouw BV | 2991 XK | 2991 XK=REGEXREPLACE(B4, "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$", "$1 $2", 0, 1)=REGEXREPLACE(B4; "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$"; "$1 $2"; 0; 1) | |
| 5 | Van Dijk BV | 2801mm | 2801 mm=REGEXREPLACE(B5, "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$", "$1 $2", 0, 1)=REGEXREPLACE(B5; "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$"; "$1 $2"; 0; 1) | |
| 6 | De Boer BV | Postbus 17 | Postbus 17=REGEXREPLACE(B6, "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$", "$1 $2", 0, 1)=REGEXREPLACE(B6; "^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$"; "$1 $2"; 0; 1) | |
| 7 |
Swipe sideways to see all columns
Formula in C2
=REGEXREPLACE(B2,"^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$", "$1 $2", 0, 1)
=REGEXREPLACE(B2;"^\s*([1-9][0-9]{3})\s*([A-Z]{2})\s*$"; "$1 $2"; 0; 1)
Result: 3011 ab
Step by step
B2holds3011ab. The pattern reads: optional white space (\s*), four digits that do not start with 0 as group 1, optional white space, two letters as group 2, optional white space, end of text. The anchors^and$make the pattern describe the whole cell.- The fourth argument is 0 (every match). It has to be written to reach the fifth, which is 1: ignore case. That is why
[A-Z]acceptsab. - The pattern matches the whole text: group 1 is
3011, group 2 isab, and the white space between them is zero characters. - The replacement
$1 $2writes group 1, one space and group 2:3011 abinC2. Without the 1,[A-Z]would not accept lowercase, nothing would match andC2would still show3011ab. - Copy
C2down.2801 mmhas a space at both ends and two inside; all of that is matched but not captured, so the result is2801 mm.2991 XKis already right and comes back the same. Postbus 17fits no part of the pattern, so it comes back asPostbus 17, without an error.
Watch out: A value that comes back unchanged is either already right or no postcode at all. Use REGEXTEST with the same pattern to tell them apart before you trust the column.
More on this example: why it works, 1 more pitfall, 1 variation
Why it works
The groups separate what to keep from what to throw away. The spaces around and between the parts are matched but not captured, so they vanish, and the replacement adds back the single space you want.
More to watch out for
- The result keeps the case of the input (
3011 ab). Lookups ignore case, so matching works; wrap the formula in UPPER if the postcode is printed in a letter.
Variations
=REGEXREPLACE(B2,"(?i)^\s*([1-9][0-9]{3})\s*([a-z]{2})\s*$", "$1 $2")
=REGEXREPLACE(B2;"(?i)^\s*([1-9][0-9]{3})\s*([a-z]{2})\s*$"; "$1 $2")
(?i) at the start of the pattern ignores case, so the fourth and fifth arguments can stay out. Same result: 3011 ab.
Advanced
Match on journal and entry number in a key
Your export tool builds one key per journal line: journal, entry number, relation and VAT percentage, separated by pipes. You follow up on the entry INK 25-0391, but entry number 25-0391 exists in two journals. You want the amount of the right one, without a helper column.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Journal key | Description | Amount | Journal and entry | Amount | ||
| 2 | VRK|25-0377|D1001|21% | Maintenance July | €12,100.00€ 12.100,00 | INK 25-0391 | €872.00€ 872,00=XLOOKUP(E2, REGEXREPLACE(A2:A6, "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|.*$", "$1 $2"), C2:C6, "No match")=XLOOKUP(E2; REGEXREPLACE(A2:A6; "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|.*$"; "$1 $2"); C2:C6; "No match") | ||
| 3 | VRK|25-0391|D1007|21% | Installation office | €4,235.50€ 4.235,50 | ||||
| 4 | INK|25-0391|C2004|9% | Catering December | €872.00€ 872,00 | ||||
| 5 | INK|25-0402|C2011|21% | Software licenses | €3,025.00€ 3.025,00 | ||||
| 6 | MEM|25-0013|-|0% | Depreciation December | €2,340.00€ 2.340,00 | ||||
| 7 |
Swipe sideways to see all columns
Formula in F2
=XLOOKUP(E2,REGEXREPLACE(A2:A6, "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|.*$", "$1 $2"), C2:C6, "No match")
=XLOOKUP(E2;REGEXREPLACE(A2:A6; "^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|.*$"; "$1 $2"); C2:C6; "No match")
Result: €872.00€ 872,00
Step by step
E2holdsINK 25-0391. The inner REGEXREPLACE runs first, on all five keys inA2:A6at once.- In each key the pattern takes three letters as group 1, a pipe (
\|, escaped because a bare|means “or”), the entry number as group 2, a second pipe and.*$, which swallows the rest of the key. The whole key is one match. - The replacement
$1 $2keeps groups 1 and 2 with a space between and drops relation and VAT. The five keys becomeVRK 25-0377,VRK 25-0391,INK 25-0391,INK 25-0402andMEM 25-0013. - That array is the lookup_array of XLOOKUP. It looks for
INK 25-0391: the first is no, the second (VRK 25-0391, same entry number but another journal) is no, the third is yes. - XLOOKUP returns the third cell of
C2:C6, which isC4: €872.00€ 872,00 inF2. - Change
E2toVRK 25-0391and the same formula returns €4,235.50€ 4.235,50, the sales entry.
Watch out: Keep only group 2 ($2) and 25-0391 finds the sales entry (€4,235.50€ 4.235,50) first and never reaches the purchase entry: no error, a wrong amount. Include everything that makes the key unique.
More on this example: why it works, 1 more pitfall, 2 variations
Why it works
XLOOKUP compares with whatever array you hand it, and REGEXREPLACE builds that array on the spot, so the source column stays as exported and no helper column is needed. The groups keep exactly the two parts that make a boekstuk unique and drop the rest.
More to watch out for
- A key that does not fit the pattern, such as
vrk|25-0391|...in lowercase, comes back unchanged and is never found, so the “No match” text shows. If the export varies in case, give 0 as the fourth and 1 as the fifth argument.
Variations
=REGEXREPLACE(A2,"^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([DC][0-9]{4})\|.*$", "$3 ($1 $2)")
=REGEXREPLACE(A2;"^([A-Z]{3})\|([0-9]{2}-[0-9]{4})\|([DC][0-9]{4})\|.*$"; "$3 ($1 $2)")
Three groups, reordered: relation first, then journal and entry. A2 becomes D1001 (VRK 25-0377). The memorial key in A6 has no relation code, so it comes back unchanged.
=FILTER(C2:C6,REGEXREPLACE(A2:A6, "^[A-Z]{3}\|([0-9]{2}-[0-9]{4})\|.*$", "$1")="25-0391")
=FILTER(C2:C6;REGEXREPLACE(A2:A6; "^[A-Z]{3}\|([0-9]{2}-[0-9]{4})\|.*$"; "$1")="25-0391")
Every line with entry number 25-0391, whatever the journal: it spills €4,235.50€ 4.235,50 and €872.00€ 872,00. Use it to see the duplicates before you pick one.
Master
Bring IBANs in different formats into one and extract them
The bank export holds the counterparty inside the description, and the IBAN in it is written however the client's system happened to print it: in groups of four, in lowercase, with dots, with hyphens or with no separators at all. For the creditor confirmation you want one clean column of IBANs, spilled from one formula, and a flag on the line that has none.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Date | Description | Amount | IBAN (clean) | ||
| 2 | 29 Dec 2025 | Aannemer Kuipers, NL91 ABNA 0417 1643 00 | €4,235.50€ 4.235,50 | NL91ABNA0417164300=LET( clean, UPPER(REGEXREPLACE(B2:B7, "[^A-Za-z0-9]", "")), IFNA(REGEXEXTRACT(clean, "NL[0-9]{2}[A-Z]{4}[0-9]{10}"), "no IBAN") )=LET( clean; UPPER(REGEXREPLACE(B2:B7; "[^A-Za-z0-9]"; "")); IFNA(REGEXEXTRACT(clean; "NL[0-9]{2}[A-Z]{4}[0-9]{10}"); "no IBAN") ) | ||
| 3 | 29 Dec 2025 | Drukkerij Van Houten nl02abna0123456789 | €872.00€ 872,00 | NL02ABNA0123456789 | ||
| 4 | 30 Dec 2025 | Lenting Kantoorservice NL86.INGB.0002.4455.88 | €1,190.40€ 1.190,40 | NL86INGB0002445588 | ||
| 5 | 30 Dec 2025 | Tuincentrum De Eik NL69-INGB-0123-4567-89 | €360.00€ 360,00 | NL69INGB0123456789 | ||
| 6 | 31 Dec 2025 | Autobedrijf Rensen NL20INGB0001234567 | €2,750.00€ 2.750,00 | NL20INGB0001234567 | ||
| 7 | 31 Dec 2025 | Gemeente Lingewaard, leges 2025-118 | €148.50€ 148,50 | no IBAN | ||
| 8 |
Swipe sideways to see all columns
Formula in E2
=LET( clean,UPPER(REGEXREPLACE(B2:B7, "[^A-Za-z0-9]", "")), IFNA(REGEXEXTRACT(clean, "NL[0-9]{2}[A-Z]{4}[0-9]{10}"), "no IBAN") )
=LET( clean;UPPER(REGEXREPLACE(B2:B7; "[^A-Za-z0-9]"; "")); IFNA(REGEXEXTRACT(clean; "NL[0-9]{2}[A-Z]{4}[0-9]{10}"); "no IBAN") )
Result: NL91ABNA0417164300 / NL02ABNA0123456789 / NL86INGB0002445588 / NL69INGB0123456789 / NL20INGB0001234567 / no IBANNL91ABNA0417164300 / NL02ABNA0123456789 / NL86INGB0002445588 / NL69INGB0123456789 / NL20INGB0001234567 / no IBAN
Step by step
- LET binds
cleanto the first expression. REGEXREPLACE gets the whole rangeB2:B7at once, so it returns six texts, one per line, and the formula spills. - The pattern
[^A-Za-z0-9]matches every character that is not a letter or a digit: spaces, dots, hyphens and commas. The replacement is an empty text, so each of them is deleted. Line 1,Aannemer Kuipers, NL91 ABNA 0417 1643 00(40 characters), becomesAannemerKuipersNL91ABNA0417164300(33 characters). - The IBAN survives because it consists only of letters and digits. Whatever separators the writer used (the spaces in line 1, the lowercase of line 2, the dots of line 3, the hyphens of line 4), the characters themselves stay and only the separators go.
- UPPER then turns everything into capitals, so line 2 becomes
DRUKKERIJVANHOUTENNL02ABNA0123456789. The six cleaned texts areAANNEMERKUIPERSNL91ABNA0417164300,DRUKKERIJVANHOUTENNL02ABNA0123456789,LENTINGKANTOORSERVICENL86INGB0002445588,TUINCENTRUMDEEIKNL69INGB0123456789,AUTOBEDRIJFRENSENNL20INGB0001234567andGEMEENTELINGEWAARDLEGES2025118. - REGEXEXTRACT now looks for
NL[0-9]{2}[A-Z]{4}[0-9]{10}in each cleaned text: NL, two digits, four capitals, ten digits, 18 characters in all. It findsNL91ABNA0417164300,NL02ABNA0123456789,NL86INGB0002445588,NL69INGB0123456789andNL20INGB0001234567. The words in front stay behind because they are not part of the match. - Line 6,
GEMEENTELINGEWAARDLEGES2025118, has noNLfollowed by two digits, four capitals and ten digits, so REGEXEXTRACT returns #N/A for that line. - IFNA replaces only that #N/A with
no IBANand leaves the five IBANs alone. The six results spill fromE2toE7. They are text, so a lookup against a column of IBANs matches them as text.
Watch out: Cleaning merges words. The text Van Houten NL followed by an invoice number would turn into VANHOUTENNL... and could produce a false match with a loose pattern. Keep the extraction pattern specific: country code, two check digits, four bank letters and exactly ten digits.
More on this example: why it works, 2 more pitfalls, 2 variations
Why it works
Cleaning first makes the extraction pattern short and strict: after the separators are gone every IBAN looks the same, so one tight pattern finds them all. Extracting first with a tolerant pattern that allows an optional space, dot or hyphen at every possible place also works, but the pattern grows long, invites mistakes, and still leaves you with separators that you must remove afterwards. Do not use REGEXREPLACE for this when your Excel has no regex functions or when the separators are known and few: a SUBSTITUTE chain with MID and FIND does the same in every version, as the second variation shows.
More to watch out for
- A foreign IBAN such as
DE89 3704 0044 0532 0130 00is cleaned but not found, because the pattern starts withNL. That line showsno IBAN. For several countries use a wider pattern such as[A-Z]{2}[0-9]{2}[A-Z0-9]{11,30}, and check the results by hand. - Without UPPER the lowercase IBAN of line 2 is not found:
[A-Z]{4}does not matchabna, so that line would showno IBAN. Keep UPPER in the formula, or begin the pattern with(?i)and accept that the result keeps its lowercase.
Variations
=IFNA(UPPER(REGEXREPLACE(REGEXEXTRACT(B2:B7,"(?i)NL[0-9]{2}[ .-]?[A-Z]{4}([ .-]?[0-9]{4}){2}[ .-]?[0-9]{2}"), "[^A-Za-z0-9]", "")), "")
=IFNA(UPPER(REGEXREPLACE(REGEXEXTRACT(B2:B7;"(?i)NL[0-9]{2}[ .-]?[A-Z]{4}([ .-]?[0-9]{4}){2}[ .-]?[0-9]{2}"); "[^A-Za-z0-9]"; "")); "")
Extract first with a tolerant pattern that allows one space, dot or hyphen between the parts, then clean only the IBAN. The result is the same, but the pattern is longer and breaks on separators it did not expect (two spaces, for example).
=IFERROR(MID(UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2," ", ""), ".", ""), "-", "")), FIND("NL", UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, " ", ""), ".", ""), "-", ""))), 18), "")
=IFERROR(MID(UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2;" "; ""); "."; ""); "-"; "")); FIND("NL"; UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2; " "; ""); "."; ""); "-"; ""))); 18); "")
When not to use REGEXREPLACE: in a version without regex functions, or when you know the separators. The SUBSTITUTE chain removes spaces, dots and hyphens, FIND locates the first NL and MID takes 18 characters. It works in every version, but it must be copied down per line, and it fails when a name before the IBAN contains NL.
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| The text comes back unchanged, with no error | The pattern does not match. It is case sensitive by default, so [A-Z] skips lowercase letters, or an anchor (^ or $) demands a shape the text does not have, such as a leading space. | Test the pattern with REGEXTEST on the same cell. Give 0 as the fourth and 1 as the fifth argument to ignore case, and allow spaces with \s* where the data may have them. |
| Far more characters change than you wanted | An unescaped . matches any character, not only a dot. Replacing . in 1.250 therefore wipes the whole text. | Write \. for a real dot, and escape the other special characters the same way: \|, \(, \?. |
| Only some matches changed, or the wrong one | occurrence is not 0. A positive number counts from the left and a negative one from the right, so 2 and -1 point at different matches as soon as a text holds three. | Use 0 for every match, and -1 when the last one is the one you need. Test with a text that has one match and with one that has three. |
| Cleaned amounts that SUM ignores | The result is text, even when it looks like a number, such as 1250 after you removed a thousands separator. | Wrap it in VALUE: VALUE(REGEXREPLACE(A2, "\.", "")). VALUE reads the decimal separator from the regional setting, so a text with a decimal comma needs a Dutch setting. |
| #NAME? in an older Excel | The workbook is opened in Excel 2021, Excel 2024 or older, which has no regular expression functions. | Keep such workbooks in Microsoft 365, paste the cleaned values as values before sending, or do the cleaning in Power Query or VBA. |