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

REPLACE

VERVANGEN in Dutch Excel

Swaps a stretch of characters at a given position and length for new text, whatever those characters are.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: C2 holds the formula
Row	A	B	C	E
1	Entry no.	Relation	Auditfile no.	20252025
2	VRK-2026-0412	D1001	VRK-2025-0412=REPLACE(A2, 5, 4, "2025")=REPLACE(A2; 5; 4; "2025")
3	INK-2026-0377	C2001
4	BNK-2026-0120	D1014
5	MEM-2024-0009	C2007

Note: E1 holds the posting year; the variation uses it. The last entry is a memorial entry from 2024 on purpose.

Result: VRK-2025-0412

=REPLACE(A2, 5, 4, "2025")

=REPLACE(A2; 5; 4; "2025")

What REPLACE does and when to use it

What it does: it swaps a stretch of characters, found by position and length, for new text, for example the year part of an entry number.

When to use it: when the part to change is defined by its place: positions 5 to 8 of a contract number, everything but the last four digits, a space after the fourth character. When it is defined by its content (every hyphen), use SUBSTITUTE.

How Excel evaluates it

Excel reads old_text and numbers its characters from 1. Then it works like this:

everything before start_num is kept
num_chars characters from start_num onwards are skipped
new_text is written in their place
whatever is left after the skipped characters is appended

Written as a sum: the result is the first start_num - 1 characters, plus new_text, plus the rest. The result is always text.

Watch out: REPLACE works on positions, not on content. num_chars is how many characters are removed, so use 0 for a pure insert; to replace a text by what it says, use SUBSTITUTE.

Mental model

Editing a line with a typewriter correction tape: you cover a stretch of a given length at a given place and type over it, and the stretch can be zero long, which is the same as squeezing in extra letters.

Syntax

Official REPLACE documentation

REPLACE(old_text, start_num, num_chars, new_text)

REPLACE(old_text; start_num; num_chars; new_text)

Arguments
Argument	Type	Meaning
`old_text`	Required	The original text you want to change; usually a cell reference.
`start_num`	Required	Position of the first character to replace, counting from 1.
`num_chars`	Required	How many characters to remove. 0 removes nothing, so new_text is inserted.
`new_text`	Required	The text to put in the gap. Use "" to delete without replacing.

Return value:Text: old_text with the chosen characters swapped for 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

Restore the posting year in entry numbers

Veldhuis Installatie BV exported its subledger entries with the export year in every entry number: VRK-2026-0412. In the auditfile the same entries carry the posting year, 2025. To match the two lists you need the 2025 version of each number, with the serial number at the end unchanged.

Sheet excerpt: C2 holds the formula
Row	A	B	C	E
1	Entry no.	Relation	Auditfile no.	20252025
2	VRK-2026-0412	D1001	VRK-2025-0412=REPLACE(A2, 5, 4, "2025")=REPLACE(A2; 5; 4; "2025")
3	INK-2026-0377	C2001
4	BNK-2026-0120	D1014
5	MEM-2024-0009	C2007
6

Formula cell (C2)InputNote: E1 holds the posting year; the variation uses it. The last entry is a memorial entry from 2024 on purpose.

Formula in C2

=REPLACE(A2, 5, 4, "2025")

=REPLACE(A2; 5; 4; "2025")

Result: VRK-2025-0412

Step by step

Excel reads old_text: A2, VRK-2026-0412, 13 characters. V, R, K are 1 to 3, the hyphen is 4, the year is 5 to 8, the second hyphen is 9, and 0412 is 10 to 13.
start_num is 5, so everything before position 5 is kept: VRK-.
num_chars is 4, so positions 5, 6, 7 and 8 are removed: 2026.
new_text 2025 goes into the gap, and the rest from position 9 onwards, -0412, is added after it. VRK- + 2025 + -0412 gives VRK-2025-0412. C2 shows it.
Copy C2 down. C3 gives INK-2025-0377 and C4 gives BNK-2025-0120. C5 gives MEM-2025-0009, although that memorial entry was posted in 2024: REPLACE does not check which year was there, it overwrites positions 5 to 8 whatever they hold.

Watch out: A number with a longer prefix, such as VRKX-2026-0412, gets 2025 written over the wrong characters: positions 5 to 8 are now -202, and the result is VRKX20256-0412. Nothing warns you; check that every number follows the same layout.

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

Why it works

The year always occupies the same four positions, so describing it by place is exact. Because REPLACE ignores the content, one formula handles every journal prefix alike; SUBSTITUTE(A2, "2026", "2025") would leave the 2024 entry alone, which is right for that row but only by accident of the content.

More to watch out for

Entries that really belong to another year, like MEM-2024-0009, are overwritten too. Filter on the year first, or the matching will show a difference that is not there.

Variations

=REPLACE(A2, 5, 4, $E$1)

=REPLACE(A2; 5; 4; $E$1)

Takes the posting year from E1 instead of from the formula, so next year you change one cell. REPLACE turns the number 2025 into text by itself.

Intermediate

Give invoice numbers one hyphen layout

You match open invoices in the receivables subledger to the general ledger. The ledger holds invoice numbers typed in two ways: 250412 without a hyphen and 25-0377 with one. The subledger uses 25-0412. Inserting a hyphen after the second character fixes the first kind but would give the second kind two hyphens.

Sheet excerpt: D3 holds the formula
Row	A	B	C	D
1	Invoice no. (GL)	Relation	Amount	Subledger layout
2	250412	D1001	€12,100.00€ 12.100,00
3	25-0377	D1014	€4,235.00€ 4.235,00	25-0377=REPLACE(SUBSTITUTE(A3, "-", ""), 3, 0, "-")=REPLACE(SUBSTITUTE(A3; "-"; ""); 3; 0; "-")
4	250398	D1001	€968.00€ 968,00
5	25-0455	D1022	€7,260.00€ 7.260,00
6

Formula cell (D3)Input

Formula in D3

=REPLACE(SUBSTITUTE(A3, "-", ""), 3, 0, "-")

=REPLACE(SUBSTITUTE(A3; "-"; ""); 3; 0; "-")

Result: 25-0377

Step by step

Excel evaluates SUBSTITUTE(A3, "-", "") first. A3 is 25-0377, 7 characters. SUBSTITUTE removes every hyphen, by content, wherever it is: 250377, 6 characters.
REPLACE receives 250377. start_num is 3, so 25 (positions 1 and 2) is kept.
num_chars is 0: nothing is removed. The 0 at position 3 stays.
new_text, a hyphen, is written in front of position 3, and 0377 follows. 25 + hyphen + 0377 gives 25-0377, 7 characters. D3 shows 25-0377.
Without SUBSTITUTE, REPLACE would insert a hyphen into 25-0377 as it is, in front of position 3: 25- + -0377 gives 25--0377 with two hyphens, 8 characters, which finds no match in the subledger.
Copied to D2, 250412 has no hyphen to remove and becomes 25-0412. D4 gives 25-0398 and D5 gives 25-0455. All four now have the same layout as the subledger.

Watch out: A number typed with a space, 25 0412, keeps the space: SUBSTITUTE only removes hyphens, so the result is 25- 0412 and still does not match. Remove the spaces in the same way before you insert.

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

Why it works

The two functions split the work by their nature. SUBSTITUTE works by content and removes the hyphen wherever it was, so every invoice number is brought to the same six-character form. REPLACE works by position and, with num_chars 0, inserts a hyphen at exactly position 3 of that uniform form.

More to watch out for

A number with a typo, such as 2504 (two digits missing), still gets its hyphen after the second character: 25-04. The formula formats, it does not validate. Check LEN of the cleaned value if that matters.

Variations

=REPLACE(SUBSTITUTE(SUBSTITUTE(A3, " ", ""), "-", ""), 3, 0, "-")

=REPLACE(SUBSTITUTE(SUBSTITUTE(A3; " "; ""); "-"; ""); 3; 0; "-")

Removes spaces as well as hyphens, for ledgers where some numbers were typed with a space.

Advanced

Mask IBANs except the edges

For the confirmation follow-up you paste the creditors' bank details into a working paper that goes to the whole audit team and to the client's finance staff. The IBANs must be masked: show the country code and check digits (the first 4 characters) and the last 4, and replace everything in between with asterisks. IBANs from different countries have different lengths, so the number of asterisks must follow the length.

Sheet excerpt: D2 holds the formula
Row	A	B	C	D
1	Relation	IBAN	Balance	Masked
2	C2001	NL27XBNK0192837465	€1,840.00€ 1.840,00	NL27*********7465=REPLACE(B2, 5, LEN(B2) - 8, REPT("", LEN(B2) - 8))=REPLACE(B2; 5; LEN(B2) - 8; REPT("*"; LEN(B2) - 8))
3	C2007	BE71XBNK12345678	€615.20€ 615,20
4	C2019	DE44XBNK00123456789012	€2,975.00€ 2.975,00
5

Formula cell (D2)InputNote: The account numbers are fictional and use a made-up bank code, XBNK.

Formula in D2

=REPLACE(B2, 5, LEN(B2) - 8, REPT("*", LEN(B2) - 8))

=REPLACE(B2; 5; LEN(B2) - 8; REPT("*"; LEN(B2) - 8))

Result: NL27**********7465

Step by step

Excel evaluates LEN(B2) first. NL27XBNK0192837465 is NL27 (4) + XBNK (4) + 0192837465 (10) = 18 characters.
LEN(B2) - 8 = 18 - 8 = 10: the 4 characters kept at the start and the 4 at the end are subtracted, and 10 remain to be hidden. Both places in the formula get this 10.
REPT("*", 10) builds new_text: ten asterisks.
REPLACE keeps positions 1 to 4, NL27, removes 10 characters from position 5 (XBNK019283, positions 5 to 14), writes the ten asterisks, and appends the rest, 7465 (positions 15 to 18).
D2 shows NL27**********7465: 4 + 10 + 4 = 18 characters, the same length as the original.
Copied to D3, the Belgian IBAN has 16 characters, 16 - 8 = 8 asterisks: BE71********5678. In D4 the German IBAN has 22, so 14 asterisks: DE44**************9012.

Watch out: An IBAN stored with spaces, NL27 XBNK 0192 8374 65, is 22 characters. The mask then hides 14 characters including spaces and shows 4 65 at the end, and the result looks masked but reveals a different set of digits. Remove spaces with SUBSTITUTE first.

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

Why it works

REPLACE does not care what the hidden characters are, only where they are and how many, which is exactly the definition of a mask. LEN makes both numbers follow the length of each IBAN, and REPT makes new_text exactly as long as the part it covers, so the masked value keeps its original length and shape.

More to watch out for

Masking in a formula does not protect anything if the original column stays in the workbook. Hide or delete column B, or paste the masked column as values into the working paper you share.
A value shorter than 8 characters makes LEN(B2) - 8 negative, and REPLACE returns #VALUE!. That is a useful signal that the cell is not an IBAN.

Variations

=LEFT(B2, 4) & REPT("*", LEN(B2) - 8) & RIGHT(B2, 4)

=LEFT(B2; 4) & REPT("*"; LEN(B2) - 8) & RIGHT(B2; 4)

Builds the mask from three pieces instead of overwriting the middle. Same result; some find it simpler to see which parts stay visible.

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

Format bank references with two inserted hyphens

In the bank statement export, the payment reference of each receipt arrives as ten characters, VRK2501234, and the general ledger holds it as VRK-25-01234. To match them you want one formula that formats the raw ones, leaves the already formatted ones alone, and cannot drift when copied. One reference was typed by hand with the hyphens, and one has a leading space from a copy-paste.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Bank reference	Amount	Ledger format
2	VRK2501234	€12,100.00€ 12.100,00	VRK-25-01234=LET( raw, TRIM(A2), isRaw, LEN(raw) = 10, withSecond, REPLACE(raw, 6, 0, "-"), formatted, REPLACE(withSecond, 4, 0, "-"), IF(isRaw, formatted, raw) )=LET( raw; TRIM(A2); isRaw; LEN(raw) = 10; withSecond; REPLACE(raw; 6; 0; "-"); formatted; REPLACE(withSecond; 4; 0; "-"); IF(isRaw; formatted; raw) )
3	INK2500377	€4,235.00€ 4.235,00
4	VRK-25-00412	€968.00€ 968,00
5	VRK2500998	€7,260.00€ 7.260,00
6

Formula cell (C2)InputMarks: extra spaceNote: A5 starts with a space. A4 was already formatted by hand.

Formula in C2

=LET(
  raw, TRIM(A2),
  isRaw, LEN(raw) = 10,
  withSecond, REPLACE(raw, 6, 0, "-"),
  formatted, REPLACE(withSecond, 4, 0, "-"),
  IF(isRaw, formatted, raw)
)

=LET(
  raw; TRIM(A2);
  isRaw; LEN(raw) = 10;
  withSecond; REPLACE(raw; 6; 0; "-");
  formatted; REPLACE(withSecond; 4; 0; "-");
  IF(isRaw; formatted; raw)
)

Result: VRK-25-01234

Step by step

raw is TRIM(A2): VRK2501234, unchanged because it has no spaces. isRaw tests LEN(raw) = 10: 3 letters + 7 digits = 10, so TRUE.
withSecond inserts the right-hand hyphen first. REPLACE(raw, 6, 0, "-") keeps positions 1 to 5 (VRK25), removes nothing, writes a hyphen, and appends 01234: VRK25-01234, 11 characters.
formatted inserts the left-hand hyphen. Position 4 is still the original position 4, because the first insert happened to its right: REPLACE(withSecond, 4, 0, "-") keeps VRK, writes a hyphen, appends 25-01234. Result VRK-25-01234, 12 characters.
isRaw is TRUE, so IF returns formatted. C2 shows VRK-25-01234.
In C4, VRK-25-00412 is already 12 characters. isRaw is FALSE, and IF returns raw unchanged. Without the test, the inserts would produce VRK--2-5-00412.
In C5, TRIM turns the 11 characters of A5 into VRK2500998, 10 characters, and the result is VRK-25-00998. Without TRIM, LEN would be 11, the row would count as formatted, and the leading space would stay in the reference, so the match with the ledger fails.

Watch out: The mirror-image mistake is inserting left to right with the same positions: REPLACE(REPLACE(raw, 4, 0, "-"), 6, 0, "-"). The first hyphen pushes everything one place to the right, so the second lands one character early: VRK-2-501234. It has two hyphens and twelve characters, so it passes a quick look.

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

Why it works

Every insert with REPLACE shifts all later positions by the length of what was inserted. Working from right to left sidesteps this: each insert happens to the right of the ones still to come and so cannot shift them, which keeps the positions you planned against the raw text valid. LET names each stage, which makes the order visible and testable.

Do not use this for values that are pure numbers: a custom number format shows the hyphens without changing the value. The alternative for text is LEFT(raw, 3) & "-" & MID(raw, 4, 2) & "-" & RIGHT(raw, 5), which reads every piece from the original and has no ordering trap, at the cost of three extractions to keep consistent when the layout changes.

More to watch out for

The isRaw test only checks the length. A ten-character value with a typo, such as VRK250123X, is still formatted as VRK-25-0123X. Validation of the content belongs in a separate check.
A hand-formatted value with a different separator, such as VRK/25/00412, is 12 characters and passes through unchanged with its slashes. If that can happen, normalize separators with SUBSTITUTE before TRIM.

Variations

=REPLACE(REPLACE(TRIM(A2), 6, 0, "-"), 4, 0, "-")

=REPLACE(REPLACE(TRIM(A2); 6; 0; "-"); 4; 0; "-")

The same right-to-left inserts without LET and without the already-formatted test; fine when every input is raw.

=LEFT(TRIM(A2), 3) & "-" & MID(TRIM(A2), 4, 2) & "-" & RIGHT(TRIM(A2), 5)

=LEFT(TRIM(A2); 3) & "-" & MID(TRIM(A2); 4; 2) & "-" & RIGHT(TRIM(A2); 5)

The alternative from the explanation: three pieces cut from the original, no position shifting to reason about.

Common mistakes

Common mistakes
Symptom	Cause	Fix
#VALUE! after swapping hyphens	You wrote REPLACE(A2, "-", 1, "/") to swap hyphens for slashes and got #VALUE!.	REPLACE wants a position where you put the text to find, and "-" is not a number. Replacing by content is SUBSTITUTE: SUBSTITUTE(A2, "-", "/").
A character disappears on insert	To insert a space you used num_chars 1, and a character disappeared.	Set num_chars to 0 when nothing should disappear.
The second hyphen sits one position off	Two nested REPLACE functions insert two hyphens, and the second one lands one position too early.	The first insert shifts every later position by one. Insert from right to left, so the positions you have not used yet are still the original ones.