How to use the Excel REPLACE function

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:

excel replace function 1 - How to use the Excel REPLACE function

Syntax:  =REPLACE(A2,1,9,B2)

Result:

excel replace function - How to use the Excel REPLACE function

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

READ:  How to use the Excel MAXA function

Add a Comment

Your email address will not be published. Required fields are marked *