Home/Formulas/Text/SUBSTITUTE
Texteasy

SUBSTITUTE()

Replaces existing text with new text in a string. Match by content.

Syntax
fx
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
Arguments
ArgumentDescription
text_to_searchThe text within which to search and replace.
search_forThe string to search for within text_to_search.
replace_withThe string that will replace search_for.
[occurrence_number]The instance of search_for within text_to_search to replace. Omit to replace all.
Example
fx
=SUBSTITUTE("a-b-c", "-", ", ")

Paste this into Google Sheets to run it. The live playground on this site covers 24 of the 61 functions, and SUBSTITUTE isn't one of them yet.

Common pitfalls

  • Replaces ALL occurrences by default. Pass the optional 4th arg occurrence_number to replace only the Nth.
  • Case-sensitive: to replace case-insensitively, use REGEXREPLACE with the (?i) flag in the pattern.
  • If search_for is empty (""), Sheets returns the original text unchanged: no error.
Related formulas
REPLACE()
Replaces part of a string with another string at a specific character position.
REGEXREPLACE()
Replaces every match of a regular expression with the replacement text.
TRIM()
Removes leading, trailing, and repeated spaces in text.
Used by
TRIM()
Removes leading, trailing, and repeated spaces in text.
REPLACE()
Replaces part of a string with another string at a specific character position.
REGEXREPLACE()
Replaces every match of a regular expression with the replacement text.
More Text formulas →Keyboard shortcuts for Sheets →