The Excel REPLACE function is a Text formula that replaces any text in a specific location with another. You can obviously use Excel's Find & Replace tool to do this, but a formula approach will give you more options as to how you can handle this, and also combine this logic with other formulas. In this guide, we’re going to show you how to use the Excel REPLACE function and also go over some tips and error handling methods.


Supported versions

  • All Excel versions

Excel REPLACE Function Syntax

REPLACE(old_text, start_num, num_chars,new_text)


Arguments

old_text

The string containing the text you want to replace.

start_num

The position of the text you want to replace in the old_text.

num_chars

The number of characters after start_num.

new_text

The text you want to replace with.


Example

=REPLACE("BMW R 1200 GS",7,4,1250)
replaces the 4 characters starting at the 7th character (1200) with a new text (1250).

 Download Workbook


Summary and Tips

  • Use the SUBSTITUTE function to replace a specific text in a text string. For example, to replace text "Power" with "Force" in a string "Unlimited Power":
    =SUBSTITUTE("Unlimited Power","Power","Force")
  • The Excel REPLACE function is case-sensitive.
  • There is VBA function that shares the same name as this function - REPLACE. However, this function has a different syntax, and works more like the SUBSTITUTE, rather than the REPLACE function.