The SUBSTITUTE() function is used to replace a specific string or content. This function is primarily used for large-scale data manipulation.
Usages :
The SUBSTITUTE() function accepts four different types of arguments. The first is Text, in which we provide the source text, and the second is the Text to be replaced. The third argument is the new text that will be placed, and the fourth argument is to determine which position the replacement will be cared for.
Syntax :
=SUBSTITUTE(text,old_text,new_text,[instance_num])
Arguments :
text – mandatory Argument, this is the source text for the function, this argument can be a constant or a cell reference.
old_text – Mandatory argument, this can be either a text constant or a cell reference.
new_text – Mandatory argument, this can be either a text constant or a cell reference.
instance_num – This argument is a optional one, this accepts only numeric value
SUBSTITUTE() Formula Supported Software products :
- Microsoft Excel
- Google Spreadsheet
- Open Office
- Zoho Excel
Examples :
Example 1 – Finding the Element and Replacing Substitute by a text :
=SUBSTITUTE(B79:B83,”Shiva”,”NK”)
In the preceding example, we used the Source text as a Range and the arguments new text and old text as text constants. This example will replace find the text “Shiva” from the given range B79:B83 with a new one. and the final output will be shown as shown above.
Example 2 – Replacing Substitute by a String with occurrence :
=SUBSTITUTE(B79:B83,”Ravi”,”Shankar”,2)
In the preceding example, we used the Substitute function to replace a text with its position of occurrence. On the Occurrence of 2, we used to replace the content “Ravi” with “Shankar.” As a result, the second occurrence will be replaced with “Shankar.”
Example 3 – Using the constant source text to replace the text :
=SUBSTITUTE(“I came from another world”,”a”,”z”,1)
In the preceding example, we used the substitute function to replace a text with another text based on a Constant value. The source text is a constant text in this case, and the element “a” is replaced with “z” on the first occurrence.
Points to be noted :
- The instance_num argument is optional; by default, the system will replace all occurrences.
- All the arguments can be text or a cell reference.
- The SUBSTITUTE() function can be used in conjunction with other formulas.
- The SUBSTITUTE() function returns a Text value.
