Syntax
REPLACE(old_text, start_num, num_chars, new_text)
| Parameter | Description |
|---|---|
| old_text | Parameter of the REPLACE function. |
| start_num | Parameter of the REPLACE function. |
| num_chars | Parameter of the REPLACE function. |
| new_text | Parameter of the REPLACE function. |
Examples
Mask credit card number
Formula
=REPLACE(A2, 1, 12, "****-****-****")
Returns: "****-****-****-5678" — replaces the first 12 characters with asterisks
Insert text at a position
Formula
=REPLACE(A2, 4, 0, "-")
Returns: "ABC-1234" — inserts a hyphen at position 4 without removing any characters (num_chars=0)
Replace a fixed-width field
Formula
=REPLACE(A2, 6, 2, "NY")
Returns: "10001NY123" — replaces 2 characters starting at position 6 with "NY"
Common Errors
#VALUE!
The start_num or num_chars is negative. Start must be >= 1 and num_chars >= 0.
#VALUE!
A numeric argument is text. Ensure position and length are numbers, not text strings.
Tips
REPLACE vs SUBSTITUTE
REPLACE works by position (character 5, length 3). SUBSTITUTE works by matching text content. Use SUBSTITUTE when you know what to replace, REPLACE when you know where.
Insert without deleting
Set num_chars to 0 to insert text without removing anything: =REPLACE(A1, 5, 0, "NEW") inserts "NEW" at position 5.
Dynamic position
Combine with FIND: =REPLACE(A1, FIND("old",A1), 3, "new") replaces at a dynamically found position.
Try REPLACE in Viztab
Import your data and use REPLACE with 370+ other formulas. No signup required.
Open Viztab