REPLACE Function
Replaces characters by position.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| old_text | The original text or cell reference containing the text to modify. | Required |
| start_num | The character position (1-based) at which to begin replacing. | Required |
| num_chars | The number of characters in old_text to replace with new_text. | Required |
| new_text | The text that replaces the specified characters. | Required |
Basic Example
Replace the middle of a string
=REPLACE("abcdef",2,3,"XYZ")Starting at position 2, three characters ("bcd") are replaced by "XYZ".
Advanced Examples
Example 1: Mask part of an account number
Cell A1 holds "1234567890"Hide the middle digits
=REPLACE(A1,4,4,"****")Example 2: Insert text without removing any
Add a dash after the first two characters of A1 = "AB1234"Use num_chars of 0 to insert
=REPLACE(A1,3,0,"-")How REPLACE Works
REPLACE identifies characters by position. It deletes num_chars characters beginning at start_num and inserts new_text in their place, then returns the rebuilt string.
Important Notes & Limitations
Replacement is positional, so it does not search for matching text the way SUBSTITUTE does.
If start_num is beyond the text length the result may be unchanged or appended depending on version.
Doubles as an insertion tool only when num_chars is 0.
Common Errors & Fixes
#VALUE! errorstart_num is less than 1 or num_chars is negative.Fix: Use a start_num of at least 1 and a non-negative num_chars.
Wrong characters changedOff-by-one in start_num or miscounting num_chars.Fix: Remember positions are 1-based and count carefully.
Download Practice File
REPLACE
replacePractice REPLACE with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the REPLACE function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps