Buzz
While navigating through your Excel files, you may find the need to transform data across multiple years with a simple maneuver. This article guides you in detail on the SUBSTITUTE function – the tool that replaces old text strings with new ones in Excel.
Description: The function replaces one or a sequence of old characters with a new text string and allows choosing the replacement at one or multiple positions.
Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num]).
In this context:
- text: The text containing the content to be replaced, a mandatory parameter.
- old_text: The old text string to be replaced, a mandatory parameter.
- new_text: The new text string replacing the old text string, a mandatory parameter.
- instance_num
Example:
Perform replacement in the following data table:
- Replace at the first found position: Want to replace the string '2016' at the 1st position in cell C6 with '2020'.
In the cell where you want to calculate, enter the formula: =SUBSTITUTE(C6,D6,E6, 1).
Press Enter, and the result is:
- Replace all occurrences:
In the cell where you want the new value, enter the formula: =SUBSTITUTE(C6,D6,E6).
Press Enter, and there you have the result:
To replace all, disregard the replacement position.
- Replacement position beyond the original string value:
Suppose you want to replace '2016' in cell C6 at the 3rd position, but '2016' is only at the 2nd position. Here's what you do:
In the cell where you want the new value, enter the formula: =SUBSTITUTE(C6,D6,E6,3).
Press Enter, and the result is returned:
So, if the replacement requirement is not present in the original string, the replacement is skipped, and the original string is returned.
The same applies to the remaining data when replacing 2 words.
Moreover, if you don't place the replacement string directly on the cells in Excel, enclose the values in double quotes to specify them as character strings. For example: SUBSTITUTE(C6,'2016','2020', 1).
Above is the usage of the SUBSTITUTE function, hoping it helps you save time in your work. Best of luck to you!
