How to Use the REPLACE Function in Excel

The Excel REPLACE function allows you to replace specific characters within a text string based on their position. Instead of searching for a particular word or character, REPLACE starts at a specified position and replaces a set number of characters with new text.

This function is especially useful when cleaning imported data, updating product codes, masking sensitive information, or modifying text strings that follow a consistent structure.

In This Guide, You’ll Learn

• What the REPLACE function does
• How to use REPLACE syntax and arguments
• How to replace characters based on position
• Practical business examples using REPLACE
• Common mistakes to avoid
• When to use REPLACE instead of SUBSTITUTE

What is the REPLACE Function?

The REPLACE function replaces part of a text string with different text based on character position. You specify where the replacement should begin, how many characters should be replaced, and the new text you want inserted.

Unlike SUBSTITUTE, which replaces matching text, REPLACE works strictly by character location.

REPLACE Function Syntax

=REPLACE(old_text,start_num,num_chars,new_text)

Argument Definitions

• old_text – The original text string.
• start_num – The position where the replacement begins.
• num_chars – The number of characters to replace.
• new_text – The text that will replace the existing characters.

Example Dataset Used in This Tutorial

You can download the sample data we’ll be using in this tutorial by clicking on this link—> REPLACE Function Data

Basic REPLACE Function

Suppose you need to update a product code.

Formulas Used

=REPLACE(A2,4,3,"999")

How This Formula Works

• The formula starts at character 4 and replaces the next 3 characters with “999”.

Updating Area Codes in Phone Numbers

Suppose phone numbers need a new area code.

Formulas Used

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

How This Formula Works

• The formula starts at character 1 and replaces the next 3 characters with “501”.

Masking Sensitive Information

REPLACE can hide part of a Social Security Number.

Formulas Used

=REPLACE(A2,1,7,"XXX-XX-")

How This Formula Works

• The formula starts at character 1 and replaces the next 7 characters with “X” leaving the last 4 digits visible.

This is useful when preparing reports that contain sensitive information.

REPLACE vs SUBSTITUTE

Although these functions appear similar, they work differently.

Function	Uses Position?	Uses Matching Text?	Best Use
REPLACE	Yes	No	Fixed-position changes
SUBSTITUTE	No	Yes	Replacing specific words or characters

Example

Using REPLACE:

=REPLACE("ABC123",4,3,"999")

Returns:

ABC999

Using SUBSTITUTE:

=SUBSTITUTE("ABC123","123","999")

Returns:

ABC999

Both produce the same result here, but REPLACE uses character positions while SUBSTITUTE searches for matching text.

Common REPLACE Mistakes

Using the Wrong Starting Position

If start_num points to the wrong character, Excel replaces the wrong portion of the text.

Counting Characters Incorrectly

Remember that spaces, punctuation marks, and symbols count as characters.

Expecting REPLACE to Search for Text

REPLACE does not search for specific words.

If you need to replace actual text values, use SUBSTITUTE instead.

Replacing Too Many Characters

Always verify your num_chars value to avoid removing more text than intended.

Example Dataset Used in This Tutorial

You can download the sample data we’ll be using in this tutorial by clicking on this link—> REPLACE Function Data

Related Articles