The SUBSTITUTE function in Google Sheets is a powerful tool for text manipulation, allowing users to replace existing text segments with new ones in a given string. This function is particularly useful for cleaning data, correcting typos, or modifying text according to specific needs.

Syntax

SUBSTITUTE(text, old_text, new_text, [instance_number])

• text: The original text string where the replacements will occur.
• old_text: The substring in the text that you want to replace.
• new_text: The text that will replace the old_text.
• [instance_number]: (Optional) Specifies which occurrence of old_text to replace. If omitted, all instances are replaced.

Example #1

SUBSTITUTE("Hello World", "World", "Everyone")

This function will replace “World” with “Everyone” in the string, resulting in: “Hello Everyone”.

Example #2

SUBSTITUTE("2021-04-01", "-", "/")

This will replace all dashes in the date string with slashes, yielding: “2021/04/01”.

Example #3

SUBSTITUTE("banana, apple, banana", "banana", "orange", 1)

Here, only the first occurrence of “banana” is replaced with “orange”, generating: “orange, apple, banana.”

Error handling

• VALUE!: Occurs when the text parameter is not a valid string or the instance_number is not valid in regard to old_text occurrences.
• N/A: Shown if the old_text is not found in the text string, indicating there’s nothing to replace.

Conclusion

In summary, the SUBSTITUTE function is an invaluable asset for users looking to perform text replacements within Google Sheets. By understanding its syntax and how to utilize it effectively through examples, users can enhance their data manipulation skills significantly. Whether correcting errors or modifying datasets, SUBSTITUTE provides a flexible solution for text management.