REPLACE Function

Summary

The Excel REPLACE function changes part of a text string based on character position. You tell Excel where to start, how many characters to replace, and what text to insert instead.

That makes REPLACE different from SUBSTITUTE. SUBSTITUTE looks for matching text content, while REPLACE works by position. If you know the exact part of the string to overwrite, REPLACE is usually the better choice.

REPLACE is especially useful when the location of the change is predictable. If you know the text starts in character 5 and runs for 3 characters, REPLACE is often simpler than searching for the old text itself. When the position is not stable, SUBSTITUTE is usually the better fit because it targets the text value instead of the character position.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text)

old_text is the original string. start_num is the first character position to replace. num_chars is how many characters to remove. new_text is what you want to insert in their place.

Arguments

• old_text - [Required] The original text string or cell reference.
• start_num - [Required] The position where the replacement begins.
• num_chars - [Required] The number of characters to remove from the original text.
• new_text - [Required] The replacement text to insert.

REPLACE vs Other Functions

REPLACE and SUBSTITUTE both modify text, but they solve different problems. Use REPLACE when you know the position. Use SUBSTITUTE when you know the text you want to change.

Using REPLACE

REPLACE is useful for masking values, updating fixed parts of a code, or inserting text at a known point. If the same segment always appears in the same position, the formula can stay simple and direct.

REPLACE also has two helpful patterns. If new_text is an empty string, the selected characters are deleted. If num_chars is 0, REPLACE inserts text without removing anything.

• Use REPLACE when you know the exact character position of the change.
• Set num_chars to 0 when you want to insert text without deleting existing characters.
• Use FIND with REPLACE when the start position changes from row to row.

Example 1 - Updating a Year in a Label

If the year always starts in the same place, REPLACE can update it with a fixed position and length. There is no need to search for the year text itself.

=REPLACE(A2, 7, 4, "2024")
// "Excel-2021" -> "Excel-2024"

Example 2 - Masking the Start of an ID

REPLACE is often used to hide part of an ID or account number. The original data remains untouched in the source cell, while the formula returns a safer display version.

=REPLACE(A3, 1, 3, "***")
// "123-456" -> "***-456"

Example 3 - Swapping a Prefix

If the prefix length is known, REPLACE can swap it out directly. This works well when a code format changes but the rest of the string should stay as it is.

=REPLACE(A2, 1, 2, "ID")
// Replaces the first 2 characters with "ID"

Example 4 - Dynamic Replacement After a Delimiter

When the text before the target section may vary in length, use FIND to calculate the start position first. That makes the replacement dynamic instead of hardcoded.

=REPLACE(A2, FIND("-",A2)+1, 4, "XXXX")
// "Excel-2021" -> "Excel-XXXX"

REPLACE always returns text. If the source value is numeric, Excel first treats it as text for the replacement. That matters if the result needs to be used in later calculations.

Conclusion Recap

REPLACE is the better choice when you know where the change should happen in the text. In this lesson, that meant updating a year, hiding part of an ID, swapping a prefix, and using FIND when the starting point was not fixed.

So the main idea is position first, content second. If you know the character number, REPLACE is usually faster to set up than searching for matching text.

• Summary: REPLACE changes text based on character position.
• Syntax: =REPLACE(old_text, start_num, num_chars, new_text).
• Key point: REPLACE works by position, not by matching text content.
• Practical usage: Masking, fixed-position updates, and position-based text edits.
• Best pattern: Combine REPLACE with FIND when the replacement position is not fixed.