SUBSTITUTE() – Replaces text within a string

SUBSTITUTE(text, oldText, newText)

• text: The original text string or a cell reference containing the text where replacement will occur.
• oldText: The specific text string you want to replace.
• newText: The new text string that will replace all occurrences of oldText.

SUBSTITUTE(T1A1, " ", "_")

Replaces all occurrences of a space character (" ") with an underscore ("_") in the text from cell T1A1.

• If T1A1 = "Text with spaces": SUBSTITUTE(T1A1, " ", "_") → "Text_with_spaces"
• If T1A1 = "apples and oranges and apples": SUBSTITUTE(T1A1, "apples", "bananas") → "bananas and oranges and bananas"
• If T1A1 = "Part-001-Part-002" and you want to remove dashes: SUBSTITUTE(T1A1, "-", "") → "Part001Part002" (replaces "-" with an empty string)
• Case sensitivity example: If T1A1 = "Data Data data": SUBSTITUTE(T1A1, "Data", "Log") → "Log Log data" (only exact matches of "Data" are replaced)

• Targeted replacement: Replaces specific occurrences of oldText with newText.
• All occurrences: Replaces all instances of oldText.
• Case-sensitive: The search for oldText is typically case-sensitive (e.g., "text" is different from "Text").
• Removal of text: Text can be removed by specifying an empty string ("") as newText.

• Standardizing data by replacing variations (e.g., replacing "Street", "St.", "Str." with "St").
• Removing unwanted characters or symbols from text.
• Replacing placeholders in template strings with actual values (though CONCAT() might also be used here).
• Anonymizing data by replacing sensitive information.

• Case sensitivity: Forgetting that the search for oldText is case-sensitive can lead to oldText not being found if the casing doesn't match.
• Unintended replacements: If oldText is a substring of other words, it might lead to unintended partial replacements (e.g., substituting "is" in "this is his list" might affect "this" and "his").