REPLACE

To replace part of a text string in Excel
Replaces characters within text

Rational

To replace part of a text string with new text

Description

The Excel REPLACE function substitute part of a text string, based on the specified starting position and number of characters, with a new text string. For example, =REPLACE(“Michael Jackson”,9,6,”Jorda”) returns “Michael Jordan”.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text)

Parameters

old_text – The text to be replaced
start_num – The first character in old_text to start replacing with new_text
num_chars – The number characters to replace with new_text
new_text – The text to replace old_text characters

Return Value

Newly updated text

Key Notes

  • The SUBSTITUTE function can also easily replace old text with a new text.
  • To detect the text to replace, use FIND or SEARCH function in case of large data set or advance requirements.
  • Space (“ ”) is counted as one of the characters while replacing the new text.
  • The Excel may return an error in case of replacing date, time or a number as the function is built for using text strings and returns a text string.

Keep Reading Similar Functions:

Merge series of multiple ranges and/or text strings in Excel

CONCAT

The CONCAT function display text by combining multiple ranges, array and/or strings. I.e., =CONCAT(“Mike “, “Tyson”) returns “Mike Tyson”, note space after “Mike ”.

Read More »
Substitutes new text for old text in a text string in Excel

SUBSTITUTE

The SUBSTITUTE function replace the specific text in a text string that occurs in a specific location. i.e, =SUBSTITUTE(“Butter”,”t”,”l”,2) return “Butler”.

Read More »
Merge multiple text strings in Excel in Excel

CONCATENATE

The CONCATENATE function display text by merging multiple text strings. I.e., combine separate texts, =CONCATENATE(“Indian “, “Ocean”) returns “Indian Ocean”.

Read More »