Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

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 »
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 »
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 »