Excel REPLACE Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 70 of 102 · Full Excel course

The Excel REPLACE function replaces a set number of characters, starting at a position you specify, with new text and returns the modified string. It works by position, not by content, which makes it the right tool when the characters to change always sit in the same place, such as an area code, a year inside a code or a fixed-width field. To replace by matching text, use SUBSTITUTE instead.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text)

Arguments

Argument	Required / Optional	Meaning
old_text	Required	The original text or cell reference.
start_num	Required	Position of the first character to replace. The first character is 1.
num_chars	Required	How many characters to remove from start_num. Use 0 to insert text without removing anything.
new_text	Required	The text to insert in place of the removed characters. It can be longer or shorter than num_chars, or an empty string to delete.

How REPLACE works

REPLACE cuts num_chars characters out of old_text beginning at start_num and pastes new_text into the gap. The lengths do not have to match: replacing three characters with ten characters simply makes the string longer. If start_num is greater than the length of old_text, new_text is appended to the end. Numbers are converted to text before processing, so the result is always text, and a start_num or num_chars below zero (or a start_num of zero) gives #VALUE!.

Step-by-step example: update a year in product codes

1. Column A holds codes such as PRD-2025-0148, where the year always occupies characters 5 to 8.
2. In B2 enter =REPLACE(A2,5,4,"2026").
3. Press Enter. The result is PRD-2026-0148; only the four characters at positions 5 to 8 changed.
4. Fill down to update every code, then paste as values if the formulas are no longer needed.

Practical use cases

1. Mask a card or account number

=REPLACE(A2,1,LEN(A2)-4,REPT("X",LEN(A2)-4))

Replaces everything except the last four characters with X, keeping the length identical.

2. Insert a character without removing anything

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

Turns AB1234 into AB1-234. A num_chars of 0 makes REPLACE an insert function.

3. Change a telephone area code

=REPLACE(A2,1,3,"020")

4. Delete a fixed-width prefix

=REPLACE(A2,1,4,"")

Removes the first four characters, the same as RIGHT(A2,LEN(A2)-4) but easier to read.

5. Convert a compact date string to a real date

=DATEVALUE(REPLACE(REPLACE(A2,5,0,"-"),8,0,"-"))

Inserts hyphens into 20260904 to make 2026-09-04, which DATEVALUE can convert.

Common mistakes and tips

• Using REPLACE when the position varies: if the text to change moves around, use SUBSTITUTE, or find the position first with FIND or SEARCH and feed it to start_num.
• #VALUE! error: start_num is 0 or negative, or num_chars is negative.
• Result is text: numbers processed by REPLACE need VALUE to become numeric again.
• Not case-related: REPLACE does not compare text at all, so case sensitivity is irrelevant.
• Multiple edits: nest REPLACE calls from left to right, remembering that an earlier insertion shifts later positions.

REPLACE compared with SUBSTITUTE

The two functions are often confused because both change part of a string. REPLACE needs to know where the characters are; SUBSTITUTE needs to know what they are. Use REPLACE for fixed-layout data such as codes, IDs, phone numbers and timestamps, where the target always occupies the same positions. Use SUBSTITUTE when you are replacing a specific word or character wherever it appears, for example changing every hyphen to a slash or removing a currency symbol. SUBSTITUTE is case-sensitive and can target a specific occurrence; REPLACE has no notion of occurrences. When neither fits, combine them: locate the text with SEARCH, then pass that position to REPLACE, which lets you replace a variable-position match while ignoring case.

Related functions

• SUBSTITUTE: replaces text by matching content rather than position.
• FIND: locates a position to feed into start_num.
• SEARCH: case-insensitive position finder with wildcards.
• REPT: builds the masking string used with REPLACE.
• See all lessons in the Excel Formulas course.

Frequently asked questions

What is the difference between REPLACE and SUBSTITUTE in Excel?

REPLACE changes characters at a given position and length. SUBSTITUTE changes characters that match a given text, wherever they occur. Use REPLACE for fixed positions and SUBSTITUTE for known text.

How do I insert text into a string with REPLACE?

Set num_chars to 0, for example =REPLACE(A2,4,0,”-“) inserts a hyphen before the fourth character without removing anything.

Why does REPLACE return #VALUE!?

Either start_num is zero or negative, or num_chars is negative. Both must be non-negative integers, and start_num must be at least 1.

Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.