In this article, you will learn how to use 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.
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.
Check the following articles if you are interested in formatting text in cells.
How to Make Text Bold, Italic, and Strikethrough in Google Sheets
How to Align Text in Google Sheets
How to Wrap Text in Cell in Google Sheets
How to Rotate Text in Google Sheets
How to Change Default Font in Google Sheets
How to Indent Texts in Google Sheets: Explained
How to Capitalize the First Letter in Google Sheets
How to Capitalize All Letters in Google Sheets
How to Make All Letters Lowercase in Google Sheets
TEXT Function in Google Sheets: Explained
Do you want to learn how to combine or split text in Google Sheets?
How to Combine Texts in Google Sheets
CONCAT Function in Google Sheets: Explained
How to Use the CONCATENATE function in Google Sheets
How to Split Texts into Columns in Google Sheets
How to Use SPLIT Function in Google Sheets
CHAR Function in Google Sheets: Explained
Do you want to know where a specify character or word is in a text string?
LEFT Function in Google Sheets: Explained
How to Use RIGHT Function in Google Sheets
SEARCH Function in Google Sheets: Explained
FIND Function in Google Sheets: Explained
Do you need to replace a part of text with another characters or words?
How to Use REPLACE Function in Google Sheets
Do you like to show a formulae in a cell?
How to Use FORMULATEXT Function in Google Sheets
This function is useful when you need to count the number of characters in a text string.
LEN Function in Google Sheets: Explained
Why don’t you double-check the spelling of your texts or cleaning up extra spaces before printing or closing Google Sheets?
How to Spell-check in Google Sheets
How to Trim Whitespaces in Google Sheets
You can repeat the same text string with this formula.
REPT Function in Google Sheets: Explained
You can learn about other Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides
Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows and update numbers in real-time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇