REPLACE Function in Microsoft Excel: Examples, Pitfalls & Compatibility
REPLACE in Microsoft Excel: replace a known character span by position rather than searching for matching words. Includes worked examples and application-specific cautions.
Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Microsoft Excel. How formula examples are checked
Quick answer: Use REPLACE in Microsoft Excel to replace a known character span by position rather than searching for matching words. The official REPLACE reference is the source for the function definition and platform-specific rules described below.
Syntax and arguments
=REPLACE(old_text, start_num, num_chars, new_text)
old_text (required): The original text or a cell containing it.
start_num (required): The starting character position; the first character is position 1.
num_chars (required): How many existing characters to remove.
new_text (required): The replacement text; it can be a different length from the removed span.
Examples use English function names, comma argument separators and decimal points. Adapt formula separators to your workbook settings, not punctuation inside quoted text.
When to use it
Use REPLACE when a file specification gives a fixed position, such as a two-character region prefix. Use SUBSTITUTE when the target is identified by its text instead. Position-based edits are fragile when the incoming format is inconsistent.
Three worked examples
Each example is independent. Where input cells are listed, enter those values first; otherwise the formula is self-contained. Expected outputs are independently checked calculations or documented error expectations, not captured results from Microsoft Excel.
Example 1: Update a fixed two-letter prefix
=REPLACE("AB-314",1,2,"ZX")
Expected result: "ZX-314" (quoted here to make spaces and text boundaries visible).
The first two characters are replaced; the hyphen and numeric suffix remain.
Example 2: Build a display mask
=REPLACE("AB-123456",4,6,"******")
Expected result: "AB-******" (quoted here to make spaces and text boundaries visible).
Six characters beginning after the prefix are replaced with six asterisks. The source remains recoverable if it is still stored elsewhere.
Example 3: Insert a separator without deleting text
=REPLACE("TRK778",4,0,"-")
Expected result: "TRK-778" (quoted here to make spaces and text boundaries visible).
A zero-length removal inserts the new text before the fourth character. The original six characters are retained.
Put it to work safely
Write down the input format and check its length before filling a replacement formula down a column. Store results in a new field and review unexpected lengths. A mask applied in a display column is not secure redaction: the original values can still be present in the workbook or its history. Remove sensitive source data through an appropriate separate process before sharing a file.
Common mistakes and fixes
The wrong characters were overwritten. Positions start at 1, not 0. Count the separators as characters and confirm the input format.
The result has a different length. The inserted text does not have to match the removed span. This is intentional, so validate the resulting identifier length when a fixed-width format is required.
The same substring occurs in several places. REPLACE does not search for occurrences. Use SUBSTITUTE for a text-based replacement rule.
Microsoft Excel-specific notes
Microsoft documents a Compatibility Version 2 change to surrogate-pair counting in REPLACE. Variation selectors can still be counted separately. Fixed positions in emoji-containing text need testing in the actual workbook compatibility version. See the vendor reference for the current definition.
Excel and Google Sheets comparison
The ASCII examples avoid differences in Unicode character counting. Confirm position boundaries again after converting a workbook that contains emoji, combining marks or other multi-code-unit characters.
Compare the REPLACE guide for Google Sheets and its official documentation. Having a guide in both libraries establishes two documented references, not a certificate of identical behavior across releases, locales or file conversions.
Related guides
Evidence and testing scope
Editorial verification: Official references retrieved on 2026-10-12. The three worked cases are included in this release’s reproducible Python checking suite. It checks the stated arithmetic, calendar or text model and the expected documented error classification; it is not the Microsoft Excel calculation engine. No native application run, UI screenshot, maximum-size benchmark or cross-version certification is claimed. Review the editorial testing standards before relying on a formula in a critical workbook.