The REPLACE function
Replaces part of a text by position. From a start character, a number of characters is swapped for new text. To replace by content instead, use SUBSTITUTE.
Syntax
REPLACE(old_text, start_num, num_chars, new_text)- old_text
- The text to change.
- start_num
- The position of the first character to replace, counting from 1.
- num_chars
- How many characters to replace. 0 inserts new_text without removing anything.
- new_text
- The text to put in their place. Empty text ("") deletes them.
Examples
B1
=REPLACE(1"2023-Q4",21,34,4"2024")| A | B | |
|---|---|---|
| 1 | 2024-Q4 |
In plain words: In 12023-Q4, replace 34 characters 2from the first one with 42024.
C1=REPLACE(A2,1,3,"")
| A | B | C | |
|---|---|---|---|
| 1 | Code | 1042 | |
| 2 | ID-1042 |
B1=REPLACE("5551234567",4,0,"-")
| A | B | |
|---|---|---|
| 1 | 555-1234567 |
Questions
What is the difference between REPLACE and SUBSTITUTE?
REPLACE works by position, such as "four characters from the start". SUBSTITUTE works by content, such as "every dash". When you know what to replace but not where it is, use SUBSTITUTE.
Can REPLACE insert text without removing any?
Yes. Set num_chars to 0, as in =REPLACE("5551234567",4,0,"-"), which puts a dash after the third digit.