Subscribe for New Post Notifications


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:

REPLACE function in Microsoft Excel


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.


=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.


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.

No comments:

Post a Comment