The SUBSTITUTE function in Excel is a powerful tool that allows users to replace specific text within a cell or range of cells with new text. This function is often used to make quick changes to large amounts of data, such as replacing a company name with a new one or updating product descriptions. It can also be used to remove unwanted characters or words from a cell or range of cells.
To use the SUBSTITUTE function, you will need to provide it with four arguments: the text you want to modify, the text you want to replace, the text you want to use as a replacement, and the instance of the text you want to replace (optional).
Here is the general syntax for the SUBSTITUTE function:
=SUBSTITUTE(text, old_text, new_text, [instance])
The text argument is the cell or range of cells containing the text you want to modify. The old_text argument is the text you want to replace within the cell or range of cells. The new_text argument is the text you want to use as a replacement for the old_text. The [instance] argument is optional and specifies which instance of the old_text you want to replace. If you leave this argument blank, the function will replace all instances of the old_text.
Let’s take a look at an example to see how the SUBSTITUTE function works. Suppose we have a spreadsheet with a list of products and their descriptions. The descriptions all include the word “delicious,” but we want to change this to “tasty” to better reflect the products. We can use the SUBSTITUTE function to make this change.
Here is the formula we would use:
=SUBSTITUTE(A2, “delicious”, “tasty”)
In this example, A2 is the cell containing the text we want to modify. “Delicious” is the text we want to replace and “tasty” is the text we want to use as a replacement. If we apply this formula to the entire range of cells containing product descriptions, it will replace all instances of “delicious” with “tasty.”
If we only want to replace a specific instance of “delicious,” we can use the [instance] argument. For example, suppose we only want to replace the second instance of “delicious” in a cell. We can use the following formula:
=SUBSTITUTE(A2, “delicious”, “tasty”, 2)
In this case, the function will only replace the second instance of “delicious” with “tasty.”
The SUBSTITUTE function is not case-sensitive, which means it will replace both “delicious” and “Delicious” with “tasty.” If you want the function to be case-sensitive, you can use the EXACT function in combination with the SUBSTITUTE function.
Here is an example of how to use the EXACT function in combination with the SUBSTITUTE function to create a case-sensitive formula:
=SUBSTITUTE(A2, EXACT(“delicious”, A2), “tasty”)
In this case, the EXACT function checks for an exact match of the old_text (“delicious”) in the text (A2). If it finds a match, it passes that text to the SUBSTITUTE function, which then replaces it with “tasty.” This formula will only replace “delicious” and not “Delicious.”