Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Text

SUBSTITUTE

SUBSTITUEREN in Dutch Excel

Replaces every occurrence of one piece of text with another, or only the occurrence you number, based on content rather than position.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: D2 holds the formula
Row	A	B	C	D
1	Relation	Name	IBAN (specification)	IBAN (clean)
2	D1001	Dekker Installatietechniek BV	NL91 ABNA 0417 1643 00	NL91ABNA0417164300=SUBSTITUTE(C2, " ", "")=SUBSTITUTE(C2; " "; "")
3	D1002	Bakker Groothandel BV	NL86 INGB 0002 4455 88
4	D1003	Van Dijk Verhuur	NL20INGB 0001 2345 67
5	D1004	Smit Metaal BV	NL44RABO0123456789

Marks: extra spaceNote: C4 has two spaces after NL20. C5 has no space at all.

Result: NL91ABNA0417164300

=SUBSTITUTE(C2, " ", "")

=SUBSTITUTE(C2; " "; "")

What SUBSTITUTE does and when to use it

What it does: it replaces a piece of text with other text wherever it occurs in a cell, for example it removes the spaces from an IBAN.

When to use it: to clean imported text, change a separator, remove a character or count how often something occurs. It is Find and Replace as a live formula. When the text is always at the same position and length, REPLACE is more direct.

How Excel evaluates it

Excel reads text and scans it from left to right for old_text, comparing case-sensitively. Each time it finds a match it counts it: first, second, third.

Without instance_num it replaces every match with new_text; with instance_num it replaces only the match with that number and leaves the others as they are.

The scan does not look inside a replacement it has already made, so replacing a with aa does not loop.

Watch out: SUBSTITUTE is case-sensitive. When old_text does not match exactly, the original text comes back unchanged and Excel shows no error.

Mental model

A proofreader with a marker who crosses out every occurrence of one word and writes another above it, or, when told a number, counts along the line and changes only that one.

Terms used on this page: cell

Syntax

Official SUBSTITUTE documentation

SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE(text; old_text; new_text; [instance_num])

Arguments
Argument	Type	Meaning
`text`	Required	The text or cell in which to replace something.
`old_text`	Required	What to look for; case-sensitive, no wildcards.
`new_text`	Required	What to put in its place; "" removes it.
`instance_num`	Optional	Which occurrence to replace; leave it out to replace all.

Return value:Text: text with old_text replaced by new_text

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

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Remove the spaces from IBANs before matching

The client's debtors specification shows IBANs in groups of four, typed by hand: NL91 ABNA 0417 1643 00, once with a double space and once without any.

The XAF auditfile holds the IBANs without spaces, so a lookup between the two never matches. In an extra column you remove every space from the IBAN.

Sheet excerpt: D2 holds the formula
Row	A	B	C	D
1	Relation	Name	IBAN (specification)	IBAN (clean)
2	D1001	Dekker Installatietechniek BV	NL91 ABNA 0417 1643 00	NL91ABNA0417164300=SUBSTITUTE(C2, " ", "")=SUBSTITUTE(C2; " "; "")
3	D1002	Bakker Groothandel BV	NL86 INGB 0002 4455 88
4	D1003	Van Dijk Verhuur	NL20INGB 0001 2345 67
5	D1004	Smit Metaal BV	NL44RABO0123456789
6

Formula cell (D2)InputMarks: extra spaceNote: C4 has two spaces after NL20. C5 has no space at all.

Formula in D2

=SUBSTITUTE(C2, " ", "")

=SUBSTITUTE(C2; " "; "")

Result: NL91ABNA0417164300

Step by step

Excel reads C2: NL91 ABNA 0417 1643 00, 22 characters, with a space in positions 5, 10, 15 and 20.
old_text is a single space. Excel scans from the left and finds four matches. Because instance_num is left out, all four are replaced.
new_text is empty, so each space is replaced by nothing. D2 shows NL91ABNA0417164300, 22 - 4 = 18 characters.
Copy D2 down. C3, NL86 INGB 0002 4455 88, has four spaces and becomes NL86INGB0002445588. C4, NL20 INGB 0001 2345 67, has five spaces (two after NL20) and becomes NL20INGB0001234567.
C5 reads NL44RABO0123456789 and has no space. There is nothing to replace, so D5 shows the IBAN as it was: NL44RABO0123456789, without an error.

Watch out: An IBAN copied from a PDF bank statement may hold a non-breaking space. SUBSTITUTE with " " does not see it, and the lookup still fails. If LEN stays at 22, replace CHAR(160) as well.

More on this example: why it works, 1 variation

Why it works

SUBSTITUTE searches by content, so it does not matter where the spaces are or how many there are. Leaving out instance_num means every match goes, and an empty new_text turns replace into remove. Rows that were already clean pass through untouched, so the same formula works for the whole specification.

Variations

=UPPER(SUBSTITUTE(C2, " ", ""))

=UPPER(SUBSTITUTE(C2; " "; ""))

Also forces the letters to capitals, for specifications where someone typed nl91 abna.

Intermediate

Change only the second hyphen in entry numbers

The XAF export numbers journal entries as journal, year and sequence, such as INK-25-0412. The client's subledger writes INK-25/0412, with a slash before the sequence, while the hyphen after the journal code must stay. Bank entries such as BNK-0415 have no year part and must come through unchanged, so you can match both lists.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Entry no.	Amount	Subledger format
2	INK-25-0412	€2,420.00€ 2.420,00	INK-25/0412=SUBSTITUTE(A2, "-", "/", 2)=SUBSTITUTE(A2; "-"; "/"; 2)
3	INK-25-0413	€845.50€ 845,50
4	BNK-0415	€12,500.00€ 12.500,00
5	VRK-25-1187	€3,630.00€ 3.630,00
6	MEM-25-0031	€975.00€ 975,00
7

Formula cell (C2)Input

Formula in C2

=SUBSTITUTE(A2, "-", "/", 2)

=SUBSTITUTE(A2; "-"; "/"; 2)

Result: INK-25/0412

Step by step

Excel reads A2, INK-25-0412, and scans it for a hyphen. It finds the first one at position 4 and counts it as occurrence 1.
instance_num is 2, so occurrence 1 is left alone. The scan continues and finds the next hyphen at position 7: occurrence 2.
That hyphen is replaced by a slash. C2 shows INK-25/0412.
Without instance_num, SUBSTITUTE(A2, "-", "/") would replace both matches and return INK/25/0412, which no longer matches the subledger.
Copy C2 down to C6. A4 is BNK-0415, which has only one hyphen. Occurrence 2 does not exist, so nothing is replaced and C4 shows BNK-0415, as the scenario requires.
C5 gives VRK-25/1187 and C6 gives MEM-25/0031. The formula finds the hyphen by content, not by position, so a longer sequence number would work the same.

Watch out: An entry number typed with an extra hyphen, such as INK-25-04-12, gets its slash in the wrong place: INK-25/04-12. No error appears; only the match fails afterwards.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

instance_num turns SUBSTITUTE from replace-all into replace-this-one. Because the count starts from the left, the second hyphen is always the one before the sequence number, whatever the length of the journal code. A missing occurrence is not an error, which is what lets the bank entries pass through.

More to watch out for

instance_num 0 is not a way to say all: it returns #VALUE!. To replace all occurrences, leave the argument out.

Variations

=REPLACE(A2, 7, 1, "/")

=REPLACE(A2; 7; 1; "/")

REPLACE by position gives the same result for INK-25-0412, but turns BNK-0415 into BNK-04/5 and breaks the moment a journal code has four letters.

Advanced

Replace the last comma in a list of entries with and

In your journal entry testing you list the manual MEM entries per account in one cell, separated by commas.

For the management letter the last comma should read as and: MEM-0031, MEM-0044 and MEM-0052. The number of entries differs per account, so you cannot hard-code which comma is the last one.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Account	Manual entries	For the letter
2	4000	MEM-0031, MEM-0044, MEM-0052	MEM-0031, MEM-0044 and MEM-0052=SUBSTITUTE(B2, ",", " and", LEN(B2) - LEN(SUBSTITUTE(B2, ",", "")))=SUBSTITUTE(B2; ","; " and"; LEN(B2) - LEN(SUBSTITUTE(B2; ","; "")))
3	4500	MEM-0017, MEM-0090
4	8000	MEM-0003
5	1300	MEM-0021, MEM-0035, MEM-0068, MEM-0071
6

Formula cell (C2)Input

Formula in C2

=SUBSTITUTE(B2, ",", " and", LEN(B2) - LEN(SUBSTITUTE(B2, ",", "")))

=SUBSTITUTE(B2; ","; " and"; LEN(B2) - LEN(SUBSTITUTE(B2; ","; "")))

Result: MEM-0031, MEM-0044 and MEM-0052

Step by step

Excel starts with the innermost function. SUBSTITUTE(B2, ",", "") removes every comma from MEM-0031, MEM-0044, MEM-0052 and returns MEM-0031 MEM-0044 MEM-0052.
LEN(B2) counts 28 characters: 8 + 1 + 1 + 8 + 1 + 1 + 8 (three entries, two commas, two spaces). LEN of the text without commas is 26.
28 - 26 = 2. Each comma is one character, so the difference is the number of commas, and the last comma is occurrence 2.
The outer SUBSTITUTE replaces occurrence 2 of the comma with the text " and". The space that followed the comma stays, so the result is MEM-0031, MEM-0044 and MEM-0052.
Copy C2 down. C3: MEM-0017, MEM-0090 is 18 characters, 17 without the comma, 1 comma, so occurrence 1 is replaced: MEM-0017 and MEM-0090. C5: 38 - 35 = 3 commas, so MEM-0021, MEM-0035, MEM-0068 and MEM-0071.
C4 fails: MEM-0003 has no comma, 8 - 8 = 0, and instance_num 0 returns #VALUE!. The first variation handles this row.

Watch out: A list with a trailing comma, such as MEM-0017, MEM-0090, from a join that included a blank cell, counts 2 commas and replaces the last one: MEM-0017, MEM-0090 and. The result looks like a sentence and is wrong.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

Removing a character and measuring how much shorter the text becomes is the standard way to count occurrences in Excel, because there is no function that counts them directly. That count is also the number of the last occurrence, which is exactly what instance_num needs. The counting trick only works this cleanly for a single character; for a longer pattern, divide the difference by the length of the pattern.

More to watch out for

An entry that itself contains a comma is counted as two entries. Keep the separator out of the data or use a different one, such as a semicolon.

Variations

=IFERROR(SUBSTITUTE(B2, ",", " and", LEN(B2) - LEN(SUBSTITUTE(B2, ",", ""))), B2)

=IFERROR(SUBSTITUTE(B2; ","; " and"; LEN(B2) - LEN(SUBSTITUTE(B2; ","; ""))); B2)

Returns the single entry as it is when there is no comma. IFERROR also hides other errors, such as an error already in B2, so use it only on clean data.

=LEN(B2) - LEN(SUBSTITUTE(B2, ",", "")) + 1

=LEN(B2) - LEN(SUBSTITUTE(B2; ","; "")) + 1

The counting trick on its own: the number of entries is the number of commas plus 1.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Normalize invoice references with ordered replacements in LET

To match the receivables subledger to the general ledger, you need the bare invoice number, such as 25-0412. The ledger descriptions hold it in several styles:

Factuur nr. 25-0412
Factuur 25-0413
Fact 25/0420
the bare number

You build one formula that applies the replacements in a fixed, readable order and copy it down.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Relation	Description as exported	Match key
2	D1001	Factuur nr. 25-0412	25-0412=LET( raw, B2, noLong, SUBSTITUTE(raw, "Factuur nr. ", ""), noShort, SUBSTITUTE(noLong, "Factuur ", ""), noFact, SUBSTITUTE(noShort, "Fact ", ""), SUBSTITUTE(noFact, "/", "-") )=LET( raw; B2; noLong; SUBSTITUTE(raw; "Factuur nr. "; ""); noShort; SUBSTITUTE(noLong; "Factuur "; ""); noFact; SUBSTITUTE(noShort; "Fact "; ""); SUBSTITUTE(noFact; "/"; "-") )
3	D1002	Factuur 25-0413
4	D1003	Fact 25/0420
5	D1004	25-0431
6

Formula cell (C2)Input

Formula in C2

=LET(
  raw, B2,
  noLong, SUBSTITUTE(raw, "Factuur nr. ", ""),
  noShort, SUBSTITUTE(noLong, "Factuur ", ""),
  noFact, SUBSTITUTE(noShort, "Fact ", ""),
  SUBSTITUTE(noFact, "/", "-")
)

=LET(
  raw; B2;
  noLong; SUBSTITUTE(raw; "Factuur nr. "; "");
  noShort; SUBSTITUTE(noLong; "Factuur "; "");
  noFact; SUBSTITUTE(noShort; "Fact "; "");
  SUBSTITUTE(noFact; "/"; "-")
)

Result: 25-0412

Step by step

raw is B2: Factuur nr. 25-0412, 19 characters.
noLong removes the 12-character pattern "Factuur nr. " as one piece: 25-0412, 7 characters. This must come before the shorter "Factuur ", otherwise the text nr. would be left behind.
noShort looks for "Factuur " and finds nothing left, so 25-0412 passes through. noFact does the same for "Fact ".
The last step replaces "/" by "-" and finds no slash, so C2 shows 25-0412.
Copy down. C3: Factuur 25-0413 (15 characters) loses "Factuur " in noShort: 25-0413. C4: Fact 25/0420 (12 characters) loses "Fact " in noFact: 25/0420, and the last step turns the slash into a hyphen: 25-0420. C5: 25-0431 has no pattern and stays 25-0431.
Every row ends with 7 characters. The result is text, which is what an exact match on the subledger key needs.

Watch out: The mirror-image mistake is removing "Factuur " first, which is how most people start. Factuur nr. 25-0412 becomes nr. 25-0412, the longer pattern no longer exists, and the key fails to match, with no error.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Each SUBSTITUTE handles one pattern, and LET gives every stage a name, so the chain reads top to bottom instead of inside out.

The order is the real design decision: when one pattern contains another, here "Factuur nr. " contains "Factuur ", the longer, more specific one must go first, otherwise the shorter one leaves a fragment behind that no later step recognizes.

Do not use this construction when the list of replacements is long or changes often: past four or five steps the formula becomes hard to maintain. The alternative is a two-column mapping table (find, replace) processed with REDUCE in Excel 2024 or Microsoft 365, or a Power Query step with Replace Values; both keep the rules as data rather than inside a formula, at the cost of a less transparent single cell.

More to watch out for

SUBSTITUTE is case-sensitive. A description exported as FACTUUR 25-0413 does not match "Factuur " and comes through as it was. Add a step for the capitals if your sources use them.
Check the result length with LEN. A row that does not come out at 7 had a pattern your chain does not know, and SUBSTITUTE will not tell you.

Variations

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "Factuur nr. ", ""), "Factuur ", ""), "Fact ", ""), "/", "-")

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2; "Factuur nr. "; ""); "Factuur "; ""); "Fact "; ""); "/"; "-")

The same chain nested without LET; it works in every version, and it reads inside out, so the first replacement is the innermost.

Common mistakes

Common mistakes
Symptom	Cause	Fix
The text comes back unchanged	SUBSTITUTE seems to do nothing: the text comes back unchanged.	old_text did not match. Check upper and lower case (SUBSTITUTE is case-sensitive) and look for a non-breaking space, CHAR(160), where you expected a normal one.
Every hyphen changed, not only one	You wanted to change one hyphen and all of them changed.	Add instance_num: SUBSTITUTE(A2, "-", "/", 2) changes only the second.
SUM ignores the cleaned amounts	After removing the thousands separators from an amount, SUM still ignores the column.	SUBSTITUTE always returns text, even when only digits are left. Wrap it in VALUE to get a number.
Wrong result for some rows only	A chain of nested SUBSTITUTEs gives a wrong result for some rows only.	The order of the replacements matters when one pattern contains another. Replace the longest, most specific pattern first; see the Master example.

Used in combinations

Split a pasted list into a clean columnSplit a list of invoice numbers pasted into one cell on the commas, strip the stray spaces with TRIM and stack the pieces into one column with TOCOL, ready for matching.Excel 2024 or later (This combination needs Excel 2024 or later)