This post will guide you how to use Google Sheets SUBSTITUTE function with syntax and examples.

Description

---

The Google Sheets SUBSTITUTE function replaces a new text string for an old text string in a text string.

The SUBSTITUTE function can be used to replace existing text with new text in a string in google sheets.  The purpose of this function is to replace text based on content. And its returned value is the processed text.

The SUBSTITUTE function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

---

The syntax of the SUBSTITUTE function is as below:

=SUBSTITUTE(text, old_text, new_text,[instance_num])

Where the SUBSTITUTE function arguments are:

• Text -This is a required argument. The text string that containing the old text to be substituted with new text string.
• Old_text – This is a required argument. The substring that you want to replace.
• New_text – This is a required argument. The new substring to replace Old_text substring with.
• Instance_num– This is an optional argument.  The number value will specify which occurrence of old substring you want to replace with new substring. By default, all occurrences of old_text are replaced.

Note:

• If Instance_numvalue is omitted, every occurrence of old substring will be replaced with new substring.
• The SUBSTITUTE function used to replace text based on matching and not position.
• The SUBSTITUTE function is case-sensitive.
• Old_text will match parts of words as well as whole words. For example, searching a string “sh” will also replace text within “sheets”.
• SUBSTITUTE function can only be used to replace one or all instances of a string within new_text in a single cell.
• SUBSTITUTE function returns text as the output. If a number output is desired, and you can try using the VALUE function in combination with this function.

Google Sheets SUBSTITUTE Function Examples

---

The below examples will show you how to use google sheets SUBSTITUTE function to replace old substring with a new substring.

#1 To substitute first instance of “google”old text string with a new text string “mygoogle” in B1 cell, just using formula:

=SUBSTITUTE(B1,”google”,” mygoogle”,1).

#2 To substitute second instance of “google”old text string with a new text string “googlefunction” in B1 cell, just using formula:

=SUBSTITUTE(B1,”google”,”googlefunction “,2)

Note: You can use the REPLACE function to replace a newly text string at a given position in a text string. And you can use the SUBSTITUTE function to replace an existing text with a new text in a string. And Using FIND function or SEARCH function to get the location of a given text string in a text sting.