REPLACE Function in Google Sheets: Examples, Pitfalls & Compatibility

=REPLACE(text, position, length, new_text)

text (required): The original text or a cell containing it.

position (required): The starting character position; the first character is position 1.

length (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.

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.

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 Google Sheets.

Example 1: Change a region field

=REPLACE("CA-875",1,2,"US")

Expected result: "US-875" (quoted here to make spaces and text boundaries visible).

Only the fixed two-character prefix changes.

Example 2: Replace a longer segment with a shorter label

=REPLACE("batch-OLD-copy",7,3,"N")

Expected result: "batch-N-copy" (quoted here to make spaces and text boundaries visible).

The three-character OLD segment becomes one character, so the result becomes two characters shorter.

Example 3: Add a code separator

=REPLACE("BOX204",4,0,"-")

Expected result: "BOX-204" (quoted here to make spaces and text boundaries visible).

Nothing is removed. The hyphen is inserted before character 4; the result remains text.

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.

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.

Google documents one-based positions and specifies that REPLACE returns text. A string of digits produced by REPLACE should not be assumed to have become a numeric value; use VALUE only when conversion is actually required. See the vendor reference for the current definition.

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 Microsoft Excel 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.

SUBSTITUTE, LEFT, MID, LEN · All Google Sheets functions.

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 Google Sheets 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.