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:
- 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!