How to use the REPLACE function in Microsoft Excel

Let's look at some Excel Replace function examples and explore how to use the Replace function as a worksheet function in Microsoft Excel:

Summary

The Excel REPLACE function replaces characters specified by position in a given text string with another text string.
The REPLACE function is a built-in function in Excel that is categorized as a Text Function.

What Does It Do?

Replaces a portion of text with a new piece of text.

Return value

The altered text.

Syntax

=REPLACE (old_text, start_num, num_chars, new_text)

Parameters or Arguments

old_text - The original string value.
start_num - The starting position in the text to replace.
num_chars - The number of characters to replace in old_text.
new_text - The text to replace old_text with.

Formatting

No special formatting is needed.

Usage notes

The REPLACE function is useful for replacing text at a known position in a given string. For example, the following formula replaces 4 characters starting at the 1st character:

=REPLACE("Tech World",1,4,"Information")//
returns "Information World"

• #NAME? - Occurs if text data entered as the Old_text argument is not enclosed in double quotation marks - row five above.
• #VALUE! - Occurs if the Start_num or Num_chars arguments are negative or contain non-numeric values - row eight above.
• Use the REPLACE function when you want to replace text based on a known location.
• Use FIND or SEARCH to find and replace text when the location is not known in advance.
• Use SUBSTITUTE to replace one or more instances of text based on content only.

Click here to learn more excel function