How to use the SUBSTITUTE function in Excel

The SUBSTITUTE function in Excel is used to replace one or more instances of a text string with another text string within a cell or a range of cells. The syntax of the function is:

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

where:

• text is the cell or text value that contains the text to be replaced.
• old_text is the text that you want to replace.
• new_text is the text that you want to use instead of the old text.
• [instance_num] is an optional argument that specifies which occurrence of the old text you want to replace. If omitted, all occurrences of the old text are replaced.

Example: Replace the word “red” with “blue” in a sentence

Suppose you have the following sentence in cell A1:

I like red apples and red roses.

To replace the word “red” with “blue” in this sentence, you can use the SUBSTITUTE function as follows:

=SUBSTITUTE(A1, "red", "blue")

This will return:

I like blue apples and blue roses.

If you only want to replace the first occurrence of “red”, you can use the fourth argument of the function and specify 1 as the instance number:

=SUBSTITUTE(A1, "red", "blue", 1)

This will return:

I like blue apples and red roses.

Similarly, if you want to replace the second occurrence of “red”, you can specify 2 as the instance number:

=SUBSTITUTE(A1, "red", "blue", 2)

This will return:

I like red apples and blue roses.

Example: Replace multiple text strings with different text strings in a cell

Suppose you have the following product name in cell A2:

Laptop 15.6" Intel Core i5 8GB RAM 256GB SSD

To replace the text strings “Laptop”, “Intel”, and “SSD” with “Notebook”, “AMD”, and “HDD” respectively, you can use the SUBSTITUTE function nested within another SUBSTITUTE function as follows:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "Laptop", "Notebook"), "Intel", "AMD"), "SSD", "HDD")

This will return:

Notebook 15.6" AMD Core i5 8GB RAM 256GB HDD

Note that the order of the nested functions matters. The innermost function is evaluated first, and then the outer functions. If you change the order of the functions, you may get a different result.