The SUBSTITUTE function in Excel is used to replace specific text in a string. It is case-sensitive and allows you to specify which occurrence of the text to replace. The syntax is:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: The original text or cell reference containing the text.
• old_text: The text you want to replace.
• new_text: The text you want to replace it with.
• instance_num (optional): Specifies which occurrence of the old_text to replace. If omitted, all occurrences are replaced.

Example 1: Replace all occurrences of “apple” with “orange” in the text “apple, banana, apple, grape”:

=SUBSTITUTE("apple, banana, apple, grape", "apple", "orange")

Result: “orange, banana, orange, grape”

Example 2: Replace only the second occurrence of “apple” with “orange”:

=SUBSTITUTE("apple, banana, apple, grape", "apple", "orange", 2)

Result: “apple, banana, orange, grape”

This function is useful for cleaning data or modifying text strings dynamically.