How to Use the SUBSTITUTE Function in Excel

This guide explains how to use SUBSTITUTE Function in Excel, including its syntax, required arguments, practical examples, and common errors.

Description: The function replaces 1 or the old character string into a new text string and allows alternatives at one or more locations.

Syntax: SUBSTITUTE (text, old_text, new_text, [instance_num]) .

Inside:

- text : The text contains the content to be replaced, required parameters.

- old_text : The old text string to be replaced, is a required parameter.

- new_text : The new text string replaces the old text string, which is a required parameter.

- instance_num : The position you want to replace, is an optional parameter.

For example:

Perform instead in the following data table:

- Replace in the first position found: Want to replace the string 2016 in the first position in cell C6 to 2020.

In the cell to calculate enter the formula: = SUBSTITUTE (C6, D6, E6, 1) .

Pressing Enter results as:

- Replace all strings found:

In the cell to get new values enter the formula: = SUBSTITUTE (C6, D6, E6) .

Pressing Enter results as:

Thus to replace all you ignore the replacement position.

- The replacement position is outside the value of the original string :

Suppose you want to replace 2016 in cell C6 in 3rd place but 2016 only in 2nd place you do the following:

In the cell to get new values enter the formula: = SUBSTITUTE (C6, D6, E6,3) .

Pressing Enter results returns:

So if the replacement request is not on the original string, the replacement is ignored and returns the original string.

Similar to the remaining data when replacing 2 words.

Also, if you do not leave the replacement string in Excel cells, you put the values in quotation marks to identify it as a string. For example: SUBSTITUTE (C6, "2016", "2020", 1) .

Above is how to use the SUBSTITUTE function to help you save time when working. Good luck!