How to Use the SUBSTITUTE Function in Excel
Today, we’ll explore the SUBSTITUTE function, a versatile tool available in both Microsoft Excel and Google Sheets. This function is designed to replace specified text within a cell with new text of your choice.
Basic Syntax
The syntax for the SUBSTITUTE function is straightforward:
=SUBSTITUTE(text, old_text, new_text, [instance_num])- text: The text string that contains the text you want to replace.
- old_text: The substring that you wish to replace within the text.
- new_text: The text that will replace the old_text.
- instance_num (optional): This parameter specifies which occurrence of the old_text you wish to replace. If this parameter is omitted, all instances of old_text will be replaced.
Examples
Example 1: Simple Replacement
Assume we have the phrase “An apple a day keeps the doctor away.” in cell A1:
To substitute “apple” with “orange”, the formula is:
=SUBSTITUTE(A1, "apple", "orange")The result will be: “An orange a day keeps the doctor away.”
Example 2: Replacing Specific Occurrence
Using the optional instance_num parameter allows you to target a specific occurrence of old_text for replacement. Consider cell A1 contains: “David,John,Jacob,John,Sarah”.
To replace the second occurrence of “John” with “Emily”, the formula would be:
=SUBSTITUTE(A1, "John", "Emily", 2)The result will be: “David,John,Jacob,Emily,Sarah”.
Example 3: Case-Insensitive Replacement
To achieve a case-insensitive replacement, you can combine the SUBSTITUTE function with LOWER or UPPER. For example:
=SUBSTITUTE(LOWER(A1), "apple", "orange")This formula first converts all text in A1 to lowercase and then performs the substitution, effectively ignoring the original text’s case.
Conclusion
The SUBSTITUTE function offers a powerful way to modify specific text within cells in Excel and Google Sheets. By mastering its syntax and exploring practical examples, you’ll be able to manipulate text data more effectively and efficiently.
More information: https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332