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:
Based on the Excel spreadsheet above, the following REPLACE examples would return: