REPLACE function
REPLACE swaps out characters at a known position in text for new text. Use SUBSTITUTE instead when you know the text to replace rather than its position.
Example
Read it as a sentence: in abcdef, start at character 2, replace the next 3 characters with X, which gives aXef.
A
B
C
D
1
aXef
2
3
Syntax
REPLACE(old_text, start_num, num_chars, new_text)
-
old_text(required) - The original text.
-
start_num(required) - Position where replacement begins.
-
num_chars(required) - How many characters to remove.
-
new_text(required) - Text to insert.
Returns: The text with that section replaced.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=REPLACE("abcdef",2,3,"X") | aXef | Basic |
=REPLACE("abc",10,1,"X") | abcX | Start past end |
=REPLACE("abc",2,0,"X") | aXbc | Insert |
=REPLACE(12345,1,2,"9") | 9345 | Number |
=REPLACE("abc",2,1,5) | a5c | Number new_text |
=REPLACE("abc",0,1,"X") | #VALUE! | Start 0 |
=REPLACE("abc",2,-1,"X") | #VALUE! | Negative length |
Watch out for
- Position-based, not content-based.
- A num_chars of 0 inserts without deleting.
Where it works
- Excel
- REPLACE is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .