MS Excel: REPLACE function to replace with new text string

The REPLACE function in Excel is used to substitute a portion of a text string with new text, based on a specific position and number of characters. It is especially useful when you need to correct formatting, update values, or modify identifiers (e.g., phone numbers, codes, or part numbers) within a cell.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text)

Parameters:

Argument	Description
old_text	The original text string you want to modify.
start_num	The position in the text where the replacement will begin (1 = first char).
num_chars	The number of characters to replace.
new_text	The text you want to insert in place of the removed characters.

Returns: A new string with the specified characters replaced.

Examples with Tables

Example 1: Replace Characters in a Word

=REPLACE("Monday", 2, 3, "ar")

Formula	Result
=REPLACE("Monday", 2, 3, "ar")	Maray

Explanation: Starting at the 2nd character, replace 3 characters ("ond") with "ar" → "M" + "ar" + "ay"

Example 2: Mask Part of a Phone Number

=REPLACE("998901234567", 5, 6, "XXXXXX")

Original Number	Formula	Result
998901234567	=REPLACE("998901234567", 5, 6, "XXXXXX")	9989XXXXXX567

Example 3: Replace Based on Cell Reference

Assume A1 contains ExcelMastery.

=REPLACE(A1, 6, 7, "Guru")

A1	Formula	Result
ExcelMastery	=REPLACE(A1, 6, 7, "Guru")	ExcelGuru

REPLACE vs SUBSTITUTE

While both are used to replace text, they differ in how:

Function	Works On	Key Difference
REPLACE	Based on position and length	Replace characters starting at a certain point
SUBSTITUTE	Based on exact match	Replaces specific text regardless of position

Example Comparison:

=REPLACE("ABC123", 4, 3, "456") → ABC456 =SUBSTITUTE("ABC123", "123", "456")→ ABC456

But:

=REPLACE("ABCABC", 4, 3, "XYZ") → ABCXYZ =SUBSTITUTE("ABCABC", "ABC", "XYZ")→ XYZXYZ

Use Cases

Scenario	How REPLACE Helps
Masking sensitive information	Replace part of an ID or number
Fixing typos by position	Correct strings programmatically
Formatting product SKUs	Modify codes based on fixed rules
Standardizing cell values	Replace specific character ranges

Summary

Feature	Description
Function Name	REPLACE
Purpose	Replace a substring at a given position
Input Type	Text
Key Feature	Replaces by position, not by matching string
Output	Modified text string

Notes

• If start_num is greater than the length of the string, Excel returns the original string unchanged.

• If num_chars is 0, the new_text is inserted at the position instead of replacing characters.

• Works well in combination with FIND, LEFT, RIGHT, MID, and LEN.

Related Functions

Function	Description
SUBSTITUTE	Replace text by matching string
MID	Extract part of a string
LEFT	Get the first N characters
RIGHT	Get the last N characters
TEXT	Format numbers as text

Final Thoughts

The REPLACE function is a powerful tool for string manipulation by position, ideal for handling structured text formats. It’s especially effective when working with data where character position matters, such as codes, IDs, and custom formats.