SUBSTITUTE Function in Google Sheets

The SUBSTITUTE function in Google Sheets replaces specific text within a string. It works inside formulas, so the original data stays untouched. The text functions guide covers the full set of text tools, and SUBSTITUTE is the formula-based alternative to Edit > Find and Replace (Ctrl+H).

This guide covers the syntax, nested replacements for cleaning data, the instance argument for targeting specific occurrences, and how SUBSTITUTE compares to REPLACE.

In This Guide

• SUBSTITUTE Syntax and Parameters
• Clean Phone Numbers Step-by-Step
• The Instance Argument
• Nested SUBSTITUTE for Multiple Replacements
• SUBSTITUTE vs REPLACE
• Practical Examples
• Common Errors and How to Fix Them
• Tips and Best Practices
• Related Google Sheets Tutorials
• Frequently Asked Questions

SUBSTITUTE Syntax and Parameters

Example: =SUBSTITUTE("(555) 123-4567", "-", " ") returns "(555) 123 4567". Every hyphen becomes a space.

To delete a character entirely, use an empty string as the replacement: =SUBSTITUTE(A2, "-", "") removes all hyphens.

Clean Phone Numbers Step-by-Step

We’ll standardize the messy phone numbers in the contacts table.

The contacts spreadsheet has phone numbers in column C in 4 formats: "(555) 123-4567", "555.987.6543", "555-234-5678", and "(555)345-6789". The goal is to strip all formatting down to 10 digits.

Select cell F2 and enter:

For "(555) 123-4567", this replaces the hyphen with a space, returning “(555) 123 4567”. The parentheses, spaces, and other characters are untouched. SUBSTITUTE only replaces what you tell it to.

To strip every non-digit character, nest multiple SUBSTITUTE calls:

Each layer removes one character type: parentheses, hyphens, periods, and spaces. The result is 5551234567. This works on every phone format in the data because it covers all the separators used.

Copy the formula down through row 9. Every phone number resolves to a clean 10-digit string regardless of the original format. For "555.987.6543", the period layer handles the dots. For "555 456 7890", the space layer removes the spaces.

The Instance Argument

By default, SUBSTITUTE replaces every occurrence. The fourth argument lets you target a specific one.

For a filename like "report.v2.xlsx", this replaces only the second period with a hyphen, returning “report.v2-xlsx”. The first period stays.

This is useful when a string has multiple delimiters and you need to change one without affecting the others.

Nested SUBSTITUTE for Multiple Replacements

When you need to replace several different characters, nest SUBSTITUTE calls. Each layer handles one replacement, processing from the inside out.

Remove all currency formatting:

For "$1,234.56", this removes the dollar sign and comma, returning "1234.56". Wrap in VALUE to convert to a number: =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", ""), " ", "")).

Standardize separators:

This converts both slashes and periods to hyphens. For "2026/05/15", it returns "2026-05-15". For "2026.05.15", same result.

SUBSTITUTE vs REPLACE

SUBSTITUTE example: =SUBSTITUTE("hello world", "world", "sheets") returns "hello sheets".

REPLACE example: =REPLACE("hello world", 7, 5, "sheets") replaces 5 characters starting at position 7, returning "hello sheets".

SUBSTITUTE is the right choice when the text you want to change is consistent. REPLACE is better when the position is consistent but the content varies.

Practical Examples

Example 1: Clean Names with Extra Spaces

Multiple spaces between words cannot be fixed with TRIM alone if you want a specific separator. SUBSTITUTE with a targeted replacement handles it:

Wait, TRIM already collapses multiple spaces to one. So TRIM alone handles this. Use SUBSTITUTE on top of TRIM when you need a different separator, like replacing spaces with underscores: =SUBSTITUTE(TRIM(A2), " ", "_") turns "SHERLOCK HOLMES" into "SHERLOCK_HOLMES".

Example 2: Build a URL Slug from a Title

Convert a title like "Monthly Sales Report" into a URL-friendly slug:

This replaces spaces with hyphens and converts to lowercase, returning “monthly-sales-report”.

Example 3: Mask a Phone Number

Show only the last 4 digits of a phone number:

For "(555) 123-4567", this replaces everything except the last 4 characters with a mask, returning “--4567”.

Common Errors and How to Fix Them

No Replacement Happens

SUBSTITUTE is case-sensitive. If the search text does not match the case in the cell, nothing changes. =SUBSTITUTE("HELLO", "hello", "hi") returns "HELLO" unchanged. Convert to a common case first: =SUBSTITUTE(LOWER(A2), "hello", "hi").

Unexpected Double Replacements

If you replace “a” with “ab” and the original text is “aa”, the result is “abab”, not “aab”. SUBSTITUTE scans left to right and does not re-process replaced text. This is rarely an issue, but be aware of it when the replacement text contains the search text.

#N/A or #VALUE! Errors

SUBSTITUTE does not return these errors on its own. If you see errors, they come from another function in the formula chain. Check the input cell. If it contains an error, SUBSTITUTE passes it through.

Tips and Best Practices

1. Use empty string "" to delete characters. =SUBSTITUTE(A2, "-", "") is the standard pattern for removing a character entirely.

2. Nest from the inside out. When nesting multiple SUBSTITUTE calls, the innermost one runs first. Put the most common replacement innermost for readability.

3. SUBSTITUTE preserves formulas. Unlike Find and Replace (Ctrl+H), SUBSTITUTE works inside formulas and does not modify the source cell. Use it in helper columns for clean data while keeping the original intact.

4. Use the instance argument for multi-delimiter strings. When a string has the same character used as different delimiters (like periods in filenames), the fourth argument targets exactly the one you want.

5. Consider REGEXREPLACE for pattern-based replacements. When you need to remove all non-digits, all non-letters, or any pattern-based replacement, one REGEXREPLACE call replaces multiple nested SUBSTITUTE calls.

Related Google Sheets Tutorials

• Text Functions in Google Sheets: Full guide to cleaning, extracting, and combining text
• FIND and SEARCH in Google Sheets: Locate text positions before replacing
• TRIM and CLEAN in Google Sheets: Remove extra spaces and invisible characters
• REGEXMATCH, REGEXEXTRACT, REGEXREPLACE: Pattern-based text matching and replacement
• LEFT, RIGHT, MID in Google Sheets: Extract specific characters by position