SUBSTITUTE Function in Google Sheets: Explained
In this article, you will learn how to use the SUBSTITUTE formula in Google Sheets.
What is the SUBSTITUTE formula in Google Sheets?
The SUBSTITUTE formula in Google Sheets is a function that allows you to search for a specific string of text in a cell and replace it with a different string of text.
For example, you can use the SUBSTITUTE formula to replace specific words or characters in a cell or multiple instances of a line of text with a different string of text.
How do you use the SUBSTITUTE formula in Google Sheets?
To use the SUBSTITUTE formula in Google Sheets, you need to specify the following four arguments in the general syntax below:
“text_to _search”, in which you want to search for and replace specific text strings. This can be the cell reference, or the manually input text enclosed in quotation marks.
“search_for” is a string of text you want to search for and replace within the original text.
“replace_with” is a string of text that you want to use as the replacement for the search string.
“occurrence_number” [Optional] defines how many “search_for” strings are replaced with “replace_with” text; by default, the formula replace all “search_for” strings with “replace_with” text strings.
For example, if you want to replace all occurrences of the word "dog" with the word "cat" in a sentence in cell A1, “A big dog plays with a small dog”, you would use the following formula:
This formula would search for the string "dog" in cell A1, and replace it with the string "cat" wherever it appears. The result should be “A big cat plays with a small cat”.
You can also use the optional fourth argument to specify the number of instances of the search string you want to replace. For example, assuming the same sentence, “A big dog plays with a small dog”, in cell A4, if you want to substitute the first instance of the search string, you will use the following formula:
This formula would replace the first instance of the string "dog" with the string "cat", but leave any additional occurrences of the search string unchanged. Thus, the outcome of the formula should be “A big cat plays with a small dog”.
You can see how the two examples mentioned above look in Google Sheets in the following screenshot.
The SUBSTITUTE formula is a powerful and versatile tool for working with text in Google Sheets. It allows you to quickly and easily search for and replace specific text strings in a cell or range of cells.
