This post will guide you how to use Google Sheets SUBSTITUTE function with syntax and examples.
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.
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.
- 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_textin 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:
#2 To substitute second instance of “google”old text string with a new text string “googlefunction” in B1 cell, just using formula:
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.