SUBSTITUTE(txt, otext, ntext, instnum)
where
- is the text or reference to cell containing text, from which certain string is to be substituted,
- is the old text to be replaced,
- is the new text that replaces the characters in old text,
- is the instance number that specifies which occurrence needs to be replaced.
SUBSTITUTE() substitutes specific instances of old text with new text in a text string.
Description
SUBSTITUTE(txt, otext, ntext, instnum)
For Example,
SUBSTITUTE("WELCOME", "COME", "DONE", 1) returns WELDONE
In above example, first instance of old text is replaced with new text.
- SUBSTITUTE function can be used to replace specific occurrences of the text.
- If is entered directly as a part of argument, it should be enclosed in quotation marks (e.g. "TEXT").
- and should be enclosed in quotation marks (e.g. "TEXT").
- If string specified in is not part of the reference string , Calci returns the original string as a result.
- Argument is optional. If omitted, all instances mentioned in old text are replaced by new text.
- should be an integer > 0, else Calci displays #NULL error message.
Examples
| SUBSTITUTE | ||
| abc123abc123 | ||
| Cost Price |
=SUBSTITUTE(A1,"TUTE", "TUTION",1) : Replaces first instance of old text with new text.
Returns SUBSTITUTION as output. =SUBSTITUTE(A2,"123","888",2) :Replaces second instance of old text with new text.
Returns abc123abc888 as output. =SUBSTITUTE(A2,"123","888") : Replaces all instances of old text with new text.
Returns abc888abc888 as output. =SUBSTITUTE(A3,"Cost", "Selling") : Returns Selling Price as output.