Difference between revisions of "Manuals/calci/SUBSTITUTE"
Jump to navigation
Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''SUBSTITUTE'''</font></font></font><font color=...") |
|||
| Line 1: | Line 1: | ||
| − | + | =SUBSTITUTE(txt, otext, ntext, instnum)= | |
| − | < | + | where |
| + | *<math>txt</math> is the text or reference to cell containing text, from which certain string is to be substituted, | ||
| + | *<math>otext</math> is the old text to be replaced, | ||
| + | *<math>ntext</math> is the new text that replaces the characters in old text, | ||
| + | *<math>instnum</math> 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 occurrence of original text is replaced with new text. | |
| − | </ | + | *SUBSTITUTE function can be used to replace specific occurrences of the text. |
| − | + | *If <math>txt</math> is entered directly as a part of argument, it should be enclosed in quotation marks (e.g. "TEXT"). | |
| − | < | + | *<math>otext</math> and <math>ntext</math> should be enclosed in quotation marks (e.g. "TEXT"). |
| + | *If string specified in <math>otext</math> is not part of the reference string <math>txt</math>, Calci returns the original string <math>txt</math> as a result. | ||
| + | *Argument <math>instnum</math> is optional. If omitted, all instances mentioned in old text are replaced by new text. | ||
| + | *<math>instnum</math> should be positive integer greater than zero, else Calci displays #NULL error message. | ||
| − | + | == Examples == | |
| − | + | <div id="6SpaceContent" class="zcontent" align="left"> | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | <div id=" | ||
{| id="TABLE3" class="SpreadSheet blue" | {| id="TABLE3" class="SpreadSheet blue" | ||
|- class="even" | |- class="even" | ||
| − | | class=" " | | + | | class="ssh1_f" |SUBSTITUTE |
| − | | | + | | class=" " | |
| − | | class=" " | | + | | class=" " | |
| − | + | ||
| − | |||
|- class="odd" | |- class="odd" | ||
| − | | class=" " | | + | | class="ssh1_f" |abc123abc123 |
| − | | class=" " | | + | | class=" " | |
| − | | class=" | + | | class=" " | |
| − | | | + | |
| − | |||
|- class="even" | |- class="even" | ||
| − | | class=" | + | | class="ssh1_f" |Cost Price |
| − | | class=" | + | | class=" " | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | | | ||
| class=" " | | | class=" " | | ||
| − | + | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|} | |} | ||
| − | + | =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. | ||
| + | |||
| + | == See Also == | ||
| + | |||
| + | *[[Manuals/calci/REPLACE | REPLACE]] | ||
| + | *[[Manuals/calci/REPT | REPT]] | ||
| + | |||
| + | ==References== | ||
| + | |||
| + | *[http://en.wikipedia.org/wiki/Autocorrection Find and Replace] | ||
| + | *[http://en.wikipedia.org/wiki/String_operations#String_substitution String Substitution] | ||
Revision as of 17:04, 10 February 2014
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.
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 occurrence of original 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 positive integer greater than zero, 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.