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”.
Syntax: =REPLACE(old_text, start_num, num_chars, new_text)
The REPLACE function syntax has the following arguments:
- Text (Required) The text or the reference to a cell containing text for which you want to substitute characters.
- Old_text (Required) The text you want to replace.
- New_text (Required) The text you want to replace old_text with.
-
Instance_num (Optional) Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
Example: Let’s look at some Excel REPLACE function examples and explore how to use the REPLACE function as a worksheet function in Microsoft Excel:
Syntax: =REPLACE(A2,1,9,B2)
Result:
Based on the Excel spreadsheet above, the following REPLACE examples would return:
Syntax: =REPLACE(A3,1,9,B3)
Result: Blaze 12/15/2002
Syntax: =REPLACE(A4,1,9,B4)
Result: Jeffery 6/12/1984
Syntax: =REPLACE(A5,1,9,B5)
Result: Kace 8/9/2014
Syntax: =REPLACE(A6,1,9,B6)
Result: Braydon 6/14/1991
Syntax: =REPLACE(A7,1,9,B7)
Result: Achilles 6/5/2009
