Skip to content

Excel function · Text

Excel REPLACE function

REPLACE swaps a given number of characters, starting at the position you choose, for new text: adding a hyphen to a code, hiding the middle of a customer number with asterisks, updating the year in a document number.

In Turkish Excel: DEĞİŞTİR

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

English Excel=REPLACE(old_text, start_num, num_chars, new_text)
Turkish Excel=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

ArgumentRequiredDescription
old_textRequiredThe text, or cell, to change.
start_numRequiredThe 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_charsRequiredHow many characters to remove. With 0 nothing is removed and the new text is inserted. It cannot be negative.
new_textRequiredThe 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.

B2fx=REPLACE(A2,4,0,"-")
Add a missing hyphen to codes: example table
AB
1Code as TypedClean Code
2KBL1525KBL-1525
3PRZ0210PRZ-0210
4SGT0016SGT-0016
5KNL2040KNL-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.

B2fx=REPLACE(A2,4,4,"****")
Hide part of a customer number: example table
AB
1Customer NoShared No
2MS-48213907MS-****3907
3MS-55120468MS-****0468
4MS-60347215MS-****7215
5MS-71902384MS-****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".

B2fx=REPLACE(A2,FIND("-",A2)+1,4,"2025")
Update the year in a document number: example table
AB
1Old NoNew No
2INV-2024-0042INV-2025-0042
3RTN-2024-0007RTN-2025-0007
4PO-2024-0113PO-2025-0113
5CRED-2024-0990CRED-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)+1 as the start and 0 as num_chars, though the & operator is shorter for that.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a column that hides characters 4 to 7 of each customer number with asterisks

Convert to table

Bring 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.

Related functions

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free