SUBSTITUTE function

The SUBSTITUTE function in Excel is used to replace occurrences of a specific substring within a text string with a new substring. This is especially helpful when you want to replace specific words, characters, or phrases within a string without affecting other content.

Syntax:

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

Parameters:

• text (required): The original text string in which you want to make substitutions.
• old_text (required): The substring (character or phrase) that you want to replace.
• new_text (required): The substring that will replace the old_text.
• instance_num (optional): The specific instance of old_text that you want to replace. If omitted, all occurrences of old_text are replaced. If a number is provided, only that occurrence is replaced (e.g., the first or second occurrence).

Key Points:

• If the instance_num is omitted, all instances of old_text in the text string will be replaced.
• If instance_num is provided, only that specific instance will be replaced. If instance_num is greater than the total number of occurrences, no substitution will be made.
• The SUBSTITUTE function is case-sensitive, so “apple” and “Apple” would be treated as different strings.

Examples of Usage:

1. Replace All Occurrences

If cell A1 contains the text "I like apples. Apples are sweet." and you want to replace all occurrences of “Apples” with “Oranges”:

=SUBSTITUTE(A1, "Apples", "Oranges")

This will return:

I like apples. Oranges are sweet.

Explanation:

• Since the instance_num is not specified, all occurrences of “Apples” are replaced with “Oranges”.

2. Replace a Specific Instance

If cell B1 contains the text "I like apples. Apples are sweet." and you want to replace only the second occurrence of “Apples” with “Oranges”, you can specify the instance_num as 2:

=SUBSTITUTE(B1, "Apples", "Oranges", 2)

This will return:

I like apples. Oranges are sweet.

Explanation:

• The instance_num argument ensures only the second occurrence of “Apples” is replaced with “Oranges”.

3. Replace a Character

If cell C1 contains the text "Hello World" and you want to replace the letter “o” with the letter “a”:

=SUBSTITUTE(C1, "o", "a")

This will return:

Hella Warld

Explanation:

• All occurrences of “o” in “Hello World” are replaced with “a”.

4. Handling Multiple Replacements

If cell D1 contains "123-45-6789", and you want to replace all dashes “-” with slashes “/”, you can use:

=SUBSTITUTE(D1, "-", "/")

This will return:

123/45/6789

Explanation:

• All occurrences of the dash character “-” are replaced with slashes “/”.

Notes:

• Case Sensitivity: SUBSTITUTE is case-sensitive, meaning it will differentiate between “apple” and “Apple”. To make the function case-insensitive, you would need to use other functions, such as LOWER or UPPER, in combination.
• Handling Missing Substrings: If the old_text is not found within the text string, SUBSTITUTE returns the original text without any changes.
• Handling Empty Strings: If old_text is empty, SUBSTITUTE returns the original string unchanged.

Related Functions:

• REPLACE: Similar to SUBSTITUTE, but replaces part of a string based on position and length rather than a substring match.
• TEXTJOIN / CONCAT: Functions to concatenate multiple text strings, often used when you need to combine multiple replacements or modifications in a more complex formula.

The SUBSTITUTE function is a powerful tool for text manipulation in Excel, particularly useful when you need to replace specific parts of a string while preserving the rest of the content.