SheetsLessons
Text function

SUBSTITUTE function in Google Sheets

SUBSTITUTE replaces specific text inside a cell with something else, everywhere it appears or just one occurrence. It's a find-and-replace that lives in a formula.

Syntax

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
text_to_search
The original text.
search_for
The text to replace.
replace_with
The new text ("" to delete).
occurrence_number
Optional. Replace only this occurrence (1 = first).

Practice SUBSTITUTE

Task 1 of 2

Remove all the dashes from the phone number in A2. Answer in B2.

B2
fx

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

SUBSTITUTE examples

Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.

ABC
1Raw nameSKUNote
2  maya   PATEL ELC-0427-NYRefund requested
3leo rossiFUR-1180-TXShipped
4AVA KIMOFC-0093-CARefund issued
Messy imported data
FormulaResultWhat it shows
=SUBSTITUTE(B2, "-", "")ELC0427NYDelete every dash.
=SUBSTITUTE(B2, "-", "/", 1)ELC/0427-NYOnly the first dash.
=SUBSTITUTE(C4, "issued", "complete")Refund completeSwap a word.

Tips and common mistakes

  • SUBSTITUTE is case-sensitive: searching for "refund" won't replace "Refund".
  • Nest several SUBSTITUTEs to replace different things in one go.

Related functions

TRIMRemoves extra spaces.FINDPosition of text inside other text (case-sensitive).TEXTJOINJoins text with a separator between each item.

Learning step by step? SUBSTITUTE is covered in Lesson 27: SUBSTITUTE Function of the free course.