How to Use SUBSTITUTE in Google Sheets: Comprehensive Guide

SUBSTITUTE replaces specific text inside a string with new text. It is the simple, exact-match cleaning tool: remove all spaces, swap a character for another, or change just the second occurrence of a word. When the text to replace has a pattern rather than a fixed value, use REGEXREPLACE instead.

Syntax

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

text_to_search: The text to modify.
search_for: The exact text to find.
replace_with: The text to put in its place.
occurrence_number [Optional]: Which occurrence to replace. If omitted, all occurrences are replaced.

SUBSTITUTE is case-sensitive: "a" does not match "A".

Basic examples

=SUBSTITUTE("Hello World", "World", "Sheets")

Returns Hello Sheets.

=SUBSTITUTE("a-b-c-d", "-", "/")

Returns a/b/c/d: every hyphen is replaced.

=SUBSTITUTE("a-b-c-d", "-", "/", 2)

Returns a-b/c-d: only the second hyphen is replaced.

Practical patterns

Remove characters

Replace with an empty string to delete:

=SUBSTITUTE(A2, " ", "")

Removes every space, including those between words.

=SUBSTITUTE(A2, "-", "")

Strips hyphens from a phone number or ID.

Remove several characters

Nest SUBSTITUTE calls:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "-", "")

For more than two or three characters, REGEXREPLACE(A2, "[()-]", "") is shorter.

Fix non-breaking spaces

Text copied from web pages often has non-breaking spaces (CHAR(160)) that TRIM can't remove and that break lookups. Replace them with normal spaces:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

Replace line breaks

=SUBSTITUTE(A2, CHAR(10), " ")

Turns line breaks within a cell into spaces. Use CHAR(10) for a line break.

Convert decimal commas to points

=VALUE(SUBSTITUTE(A2, ",", "."))

Useful when numbers imported from another locale arrive as text with commas.

Count how many times text appears

=(LEN(A2) - LEN(SUBSTITUTE(A2, "a", ""))) / LEN("a")

The length lost by removing a string, divided by that string's length, gives the number of occurrences. Note that this is case-sensitive; wrap A2 in LOWER for a case-insensitive count.

Count the words in a cell

=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1

Replace the last occurrence

Replace the nth occurrence where n is the total count:

=SUBSTITUTE(A2, "-", "/", LEN(A2) - LEN(SUBSTITUTE(A2, "-", "")))

Apply to a whole column

=ARRAYFORMULA(SUBSTITUTE(A2:A, "-", ""))

Common errors

Nothing changes. Case mismatch ("US" vs "us"), or the text contains a non-breaking space or invisible character. Check with CODE(MID(A2, n, 1)).
Numbers become text. The output is text, even when it looks like a number. Wrap with VALUE to calculate with it.
Leading zeros vanish. If a cell is a number, 007 is already 7. Format the source as plain text first.
Too much replaced. SUBSTITUTE replaces every match. Use occurrence_number to limit it.
Wildcards do not work. * and ? are literal here. Use REGEXREPLACE for patterns.

SUBSTITUTE vs. REPLACE vs. REGEXREPLACE

SUBSTITUTE: replace specific text, wherever it appears.
REPLACE: replace characters by position and length, regardless of what they are.
REGEXREPLACE: replace text that matches a pattern, with case-insensitive and group-based options.

Frequently asked questions

Is SUBSTITUTE case-sensitive? Yes. For case-insensitive replacement, use REGEXREPLACE with (?i).

Does it support wildcards? No. Use REGEXREPLACE.

Can I replace several different texts at once? Nest calls, or use REGEXREPLACE with |, such as REGEXREPLACE(A2, "cat|dog", "pet").

What does it do if the search text isn't found? It returns the original text unchanged.

Related Functions

REGEXREPLACE: Replace text by pattern.
TRIM: Remove extra spaces.
SPLIT: Divide text at a delimiter.
LEN: Return the length of a text.
VALUE: Convert text to a number.

SUBSTITUTE