Google Sheets SUBSTITUTE Function: Syntax, Examples & Tips

text_to_search:
The text or cell reference that contains the text you want to search within.

search_for:
The text that you want to find and replace within the text_to_search.

replace_with:
The text that you want to use as a replacement for the search_for text.

occurrence_number:
(Optional) Specifies which occurrence of the search_for text you want to replace. If omitted, all occurrences will be replaced.

What is a SUBSTITUTE Function?

The SUBSTITUTE function in Google Sheets is a useful tool that allows you to replace existing text within a string with new text. This function is particularly handy when you need to make specific changes to text data without altering the entire string. Unlike other text functions, SUBSTITUTE can replace all instances of a specified substring or just a specific occurrence, giving you flexibility in how you manipulate your text.

In simpler terms, SUBSTITUTE helps you modify text by swapping out certain words or characters for others in a straightforward way.

A Practical Example

Imagine you are managing a list of customer feedback and you want to standardize the feedback by replacing a specific term:

Feedback Table:

You want to replace the word "great" with "excellent" to enhance the feedback consistency.

SUBSTITUTE Formula

To achieve this, you would use the SUBSTITUTE function as follows:

=SUBSTITUTE(B2, "great", "excellent")

In this formula:

• B2 is the cell containing the feedback text.
• "great" is the text you want to replace.
• "excellent" is the new text that will replace the old text.

Result of the Formula

When you apply the SUBSTITUTE formula to the Feedback Table, the output would look like this:

In this case, the SUBSTITUTE function has replaced all instances of "great" with "excellent" in the feedback text.

Why Use SUBSTITUTE?

SUBSTITUTE is beneficial because it simplifies the process of modifying text data in Google Sheets. It allows for precise changes without the need for complex formulas or manual edits. Additionally, it can be used in combination with other text functions to create more advanced text manipulation scenarios.

Key Takeaways:

• SUBSTITUTE: A powerful text function that replaces specified text within a string.
• Flexibility: Can replace all instances or a specific occurrence of the text.
• Efficiency: Saves time by automating text modifications in your data.
• Common Use Cases: Ideal for standardizing text, correcting typos, or updating terminology in datasets.

SUBSTITUTE is an essential function for anyone working with text data in Google Sheets, providing a straightforward way to enhance data quality and consistency.

Happy spreadsheeting!