The SUBSTITUTE function in Excel is a powerful tool for manipulating text strings by replacing specific instances of a substring with another. This function is particularly useful when you need to replace certain characters or words within a cell with something else. In this guide, we’ll walk you through how to use the Excel SUBSTITUTE function step by step, and provide tips and error handling methods to ensure smooth execution. Whether you're looking to replace single characters, multiple occurrences, or specific instances of text within your data, the SUBSTITUTE function can help streamline your text processing tasks in Excel.
Excel REPLACE Function vs. Excel SUBSTITUTE Function
The Excel REPLACE and SUBSTITUTE functions are closely related, both serving the purpose of replacing text within a string. However, they differ in their specific applications:
SUBSTITUTE is used to replace one or more instances of a given character or text string within a cell. If you know the text to be replaced and want to replace all occurrences, the Excel SUBSTITUTE function is the appropriate choice. REPLACE, on the other hand, is used to change characters in a specified position of a text string. If you know the position of the character(s) to be replaced and want to replace characters at specific positions, the Excel REPLACE function is the suitable option. Additionally, the SUBSTITUTE function in Excel allows for an optional parameter (instance_num), which specifies which occurrence of old_text should be replaced with new_text.
These functions are valuable tools for manipulating text in Excel, offering flexibility in text replacement tasks. We hope these examples prove helpful in your Excel endeavors.
Excel SUBSTITUTE Function: Supported versions
- All Excel versions
Excel SUBSTITUTE Function: Syntax
Arguments
| text | The string where you want to do the substitution. |
| old_text | The text you want to substitute. |
| new_text | The text you want to substitute the old_text with. |
| [instance_num] | Optional. The instance of old_text you want to substitute. If omitted, all instances of old_text will be replaced with new_text. |
Examples
Substitute all instances
When using the SUBSTITUTE function in Excel, if you omit the [instance_num] argument, Excel replaces all instances of the old_text with the new_text. For instance, in the formula =SUBSTITUTE(A2, "Power", "Force"), if the cell A2 contains the text "Power is powerful," both instances of the word "Power" will be replaced with "Force," resulting in the text "Force is forceful." This functionality makes the SUBSTITUTE function a powerful tool for text manipulation in Excel, allowing for comprehensive replacements within a given text string.
Substitute specific instance
By setting the [instance_num] argument to 2 in the SUBSTITUTE function in Excel, you can specify that only the second instance of the specified text should be replaced. For example, in the formula =SUBSTITUTE(A2, "Power", "Force", 2), if the cell A2 contains the text "Power is powerful, and Power is strong," only the second instance of the word "Power" will be replaced with "Force," resulting in the text "Power is powerful, and Force is strong." This level of control over the replacement process allows for precise manipulation of text strings in Excel, making the SUBSTITUTE function a valuable tool for various text-related tasks.
Excel SUBSTITUTE Function: Summary and Tips
- Use the REPLACE function to replace any text in a specific location. For example, to replace a 5 character text starting from the 4th character: =REPLACE(old_text,4,5,new_text)
- The Excel SUBSTITUTE function is case-sensitive.
- To remove a specific character or text, replace it with an empty string (""). =SUBSTITUTE(text," ","")
- Use the SUBSTITUTE function in a nested form to substitute different characters with a single formula. For example, to remove space and dash characters, use: =SUBSTITUTE(SUBSTITUTE(text," ",""),"-","")
