What it does
REPLACE is the position-based sibling of SUBSTITUTE. Instead of saying what to change, you say where: the character to start at and how many characters to remove. The new text goes where the old characters were, and it can be shorter or longer than what it replaces.
If num_chars is 0, nothing is removed and the new text is inserted: =REPLACE("KBL1525",4,0,"-") returns "KBL-1525". It is the quickest way to fix codes that are missing a delimiter.
If you know the content of the part rather than its position, SUBSTITUTE is the better choice. If the position changes from row to row, calculate the start with FIND.
Syntax
=REPLACE(old_text, start_num, num_chars, new_text)=DEĞİŞTİR(eski_metin; başlangıç; karakter_sayısı; yeni_metin)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
old_text | Required | The text, or cell, to change. |
start_num | Required | The character where the replacement starts; the first character is 1. If it is beyond the end of the text, the new text is added at the end. |
num_chars | Required | How many characters to remove. With 0 nothing is removed and the new text is inserted. It cannot be negative. |
new_text | Required | The text to write in place of the removed characters. With empty text "" the characters are simply deleted. |
Examples
1Add a missing hyphen to codes
The codes were typed without a hyphen, as "KBL1525". num_chars is 0, so nothing is removed and the hyphen goes in before the fourth character.
=REPLACE(A2,4,0,"-")| A | B | |
|---|---|---|
| 1 | Code as Typed | Clean Code |
| 2 | KBL1525 | KBL-1525 |
| 3 | PRZ0210 | PRZ-0210 |
| 4 | SGT0016 | SGT-0016 |
| 5 | KNL2040 | KNL-2040 |
- B2 (filled down to B5)
=REPLACE(A2,4,0,"-")
B2 shows KBL-1525 and B5 shows KNL-2040.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Hide part of a customer number
In a list that will be shared outside the company, characters 4 to 7 of the customer numbers should not be visible. Those four characters are removed and four asterisks take their place.
=REPLACE(A2,4,4,"****")| A | B | |
|---|---|---|
| 1 | Customer No | Shared No |
| 2 | MS-48213907 | MS-****3907 |
| 3 | MS-55120468 | MS-****0468 |
| 4 | MS-60347215 | MS-****7215 |
| 5 | MS-71902384 | MS-****2384 |
- B2 (filled down to B5)
=REPLACE(A2,4,4,"****")
B2 shows MS-****3907; the last four digits are still enough to tell the customers apart.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Update the year in a document number
The prefixes have different lengths, so FIND locates where the year starts; the four characters from there are replaced with "2025".
=REPLACE(A2,FIND("-",A2)+1,4,"2025")| A | B | |
|---|---|---|
| 1 | Old No | New No |
| 2 | INV-2024-0042 | INV-2025-0042 |
| 3 | RTN-2024-0007 | RTN-2025-0007 |
| 4 | PO-2024-0113 | PO-2025-0113 |
| 5 | CRED-2024-0990 | CRED-2025-0990 |
- B2 (filled down to B5)
=REPLACE(A2,FIND("-",A2)+1,4,"2025")
Short or long prefix, only the year changes: B2 shows INV-2025-0042 and B4 shows PO-2025-0113.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: start_num is less than 1 or num_chars is negative, or the FIND that calculates the start did not find what it was looking for.
Fix: Check the position and the number of characters; wrap the formula in IFERROR for rows without a match.
- Text in the wrong place
Cause: The texts have different lengths, so a fixed start lands in the middle of another part on some rows.
Fix: Calculate the start from a delimiter:
=REPLACE(A2,FIND("-",A2)+1,4,"2025").
Good to know
- If you pass a number, REPLACE turns it into text first and the result is text; use
--to make it a number again. - To add something to the end of a text, use
LEN(A2)+1as the start and 0 as num_chars, though the&operator is shorter for that.
In Nexell
Get Nexell to do it from a sentence
Add a column that hides characters 4 to 7 of each customer number with asterisks
Convert to tableBring your customer list into the tool and type this. Nexell builds the new column with a real Excel text formula and shows it in the preview first; the original numbers stay untouched in their own column.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I insert characters with REPLACE?
Set num_chars to 0; nothing is removed and the new text goes in at the start position: =REPLACE(A2,4,0,"-") adds a hyphen before the fourth character.
What is the difference between REPLACE and SUBSTITUTE?
REPLACE works by position: "start at character 4 and change 4 characters". SUBSTITUTE works by content: "wherever 2024 appears, write 2025". Use REPLACE for codes with a fixed structure and SUBSTITUTE when you know the text to change.