Excel REPLACE function
Excel REPLACE function

Summary 

The Excel REPLACE function replaces characters specified by location in a given text string with another text string. For example =REPLACE(“XYZ123″,4,3,”456”) returns “XYZ456”.

Purpose 
Replace text based on location

Return value 
The altered text.

Syntax 
=REPLACE (old_text, start_num, num_chars, new_text)
Arguments 
  • old_text – The text to replace.
  • start_num – The starting location in the text to search.
  • num_chars – The number of characters to replace.
  • new_text – The text to replace old_text with.
Version 
Usage notes 

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

=REPLACE("XYZ123",4,3,"456") // returns "XYZ456"

You can use REPLACE to remove text by specifying an empty string (“”) as new text. The formula below uses REPLACE to remove the first character from the string “XYZ”:

=REPLACE("XYZ",1,1,"") // returns "YZ"

Excel has several functions that can be used to replace text. Below is a brief summary. Also see examples below.

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

https://exceljet.net/excel-functions/excel-replace-function