REPLACE edits text at a known character position. It can replace, insert or remove characters without changing the source cell. Use SUBSTITUTE when the target is identified by its text instead.
REPLACE function syntax
=REPLACE(text, position, length, new_text)- position: first character to replace, counting from one.
- length: number of original characters to remove.
- new_text: replacement string; an empty string deletes the selected characters.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Text |
|---|
| ‘0000111122224455 |
| 202-555-0143 |
| 2024-Q3-1234 |
| (202) 555-0143 |
| ELC-44120 |
Keep text examples as text. Prefix a numeric-looking string with an apostrophe when entering it manually; that apostrophe is an entry marker, not part of the stored value.
Mask the beginning of an example ID
Enter this formula in A10. The fictional ID is text so every digit is retained. Starting at one, replace twelve original characters with the masking label.
=REPLACE(A2,1,12,"XXXX-XXXX-XXXX-")Result: XXXX-XXXX-XXXX-4455.
Replace the middle phone digits
The first dash occupies position four. The next three characters start at five, so that is the slice to replace.
=REPLACE(A3,5,3,"XXX")Result: 202-XXX-0143.
Update a year prefix
Replacing four characters at the beginning preserves the quarter and identifier. Check that all source codes have the expected structure.
=REPLACE(A4,1,4,"2026")Result: 2026-Q3-1234.
Keep parentheses around a masked area code
The opening parenthesis is character one. Replacement starts at two, preserving the surrounding punctuation.
=REPLACE(A5,2,3,"XXX")Result: (XXX) 555-0143.
Change a category prefix
Only the first three characters are replaced. SUBSTITUTE is another option when every input uses the same literal prefix.
=REPLACE(A6,1,3,"HMA")Result: HMA-44120.
Insert text without removing characters
A length of zero inserts at the chosen position. An empty replacement with a positive length instead removes a slice.
=REPLACE("ABC123",4,0,"-")Result: ABC-123.
Other Google Sheets articles you may also like