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