Manuals/calci/SUBSTITUTE

SUBSTITUTE (Text,OldText,NewText,InstanceNumber)

where

• $Text$ is the text or reference to cell containing text, from which certain string is to be substituted,
• $OldText$ is the old text to be replaced,
• $NewText$ is the new text that replaces the characters in old text,
• $InstanceNumber$ 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 (Text,OldText,NewText,InstanceNumber) 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 $Text$ is entered directly as a part of argument, it should be enclosed in quotation marks (e.g. "TEXT").
• $OldText$ and $NewText$ should be enclosed in quotation marks (e.g. "TEXT").
• If string specified in $OldText$ is not part of the reference string $Text$, Calci returns the original string $Text$ as a result.
• Argument $InstanceNumber$ is optional. If omitted, all instances mentioned in old text are replaced by new text.
• $InstanceNumber$ 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.

Related Videos

SUBSTITUTE

See Also

• REPLACE
• REPT

References

• Find and Replace
• String Substitution

• List of Main Z Functions

• Z3 home