SUBSTITUTE function - The function replaces text strings in Excel

In the course of working you have excel data file need to change a series of data over the years. With a simple operation, you can change all that data. The following article details the SUBSTITUTE function - The function replaces the text string in excel.

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:

SUBSTITUTE function - The function replaces text strings in Excel Picture 1

- 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) .

SUBSTITUTE function - The function replaces text strings in Excel Picture 2

Pressing Enter results as:

SUBSTITUTE function - The function replaces text strings in Excel Picture 3

- Replace all strings found:

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

SUBSTITUTE function - The function replaces text strings in Excel Picture 4

Pressing Enter results as:

SUBSTITUTE function - The function replaces text strings in Excel Picture 5

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) .

SUBSTITUTE function - The function replaces text strings in Excel Picture 6

Pressing Enter results returns:

SUBSTITUTE function - The function replaces text strings in Excel Picture 7

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.

SUBSTITUTE function - The function replaces text strings in Excel Picture 8

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!