writer
2,661
edits
Changes
Jump to navigation
Jump to search
Line 1:
Line 1: − <div id="6SpaceContent" class="zcontent" align="left">+ − + + + + + − <font color="#484848"><font face="Arial, sans-serif"><font size="2">''''''</font></font></font>+ − <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''where txt'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2"> is the text that want to substitute characters and o</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''d_txt'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2"> is the text that want to replace and</font></font></font>+ − <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''nw_txt'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2"> is the text that want to replace od_txt with and</font></font></font>+ − <font color="#484848"><font face="Arial, sans-serif"><font size="2">'''inst_n is the '''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">occurrence of od_txt that want to replace with nw_txt. </font></font></font>+ − </div>+ − ---- − <div id="1SpaceContent" class="zcontent" align="left"> − <font color="#484848"><font face="Arial, sans-serif"><font size="2">It replaces new text for old text in a text string. </font></font></font>+ − + − ----+ − + + + + − SUBSTITUTE+ − </div></div>+ − ---- − <div id="8SpaceContent" class="zcontent" align="left"> <font color="#484848"><font face="Arial, sans-serif"><font size="2">Let’s see an example in (Column1 Row 1)</font></font></font> − − <font color="#484848"><font face="Arial, sans-serif"><font size="2">C1R1 = Cost Price</font></font></font> − − <font color="#484848"><font face="Arial, sans-serif"><font size="2">I.e.=SUBSTITUTE(C1R1,”Cost”,”Selling”) is</font></font></font> − − <font color="#484848"><font face="Arial, sans-serif"><font size="2">Selling Price</font></font></font> − − </div> − ---- − <div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div> − ---- − <div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div> − ---- − <div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div> − ---- − − + − + − + − | Column3+ − | Column4 − + − + − + − + − | class=" sshl_f " | − + − + − <div id="2Space_Copy" title="Click and Drag over to AutoFill other cells."></div> − | class=" SelectTD SelectTD" | − <div id="2Space_Handle" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" title="Click and Drag over to AutoFill other cells."></div> − | − | − |- class="odd" − | Row3 − | class=" sshl_f " | − | − | − | − |- class="even" − | Row4 − | − | − − |- class="odd"+ − | class=" " | Row5 − | − | − | − | − |- class="even" − | Row6 − | − | − | − | − <div align="left">[[Image:calci1.gif]]</div></div>+ − ----+ + + + + + + + + + + + +
Manuals/calci/SUBSTITUTE (view source)
Revision as of 22:04, 10 February 2014
, 22:04, 10 February 2014no edit summary
=SUBSTITUTE(txt, otext, ntext, instnum)=
<font color="#484848"><font face="Arial, sans-serif"><font size="2">'''SUBSTITUTE'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">(</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''txt'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">,</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''od_txt'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">,</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">'''nw_txt'''</font></font></font><font color="#484848"><font face="Arial, sans-serif"><font size="2">,inst_n)</font></font></font>
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.
</div>
*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").
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
*<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="2SpaceContent" class="zcontent" align="left">
{| id="TABLE3" class="SpreadSheet blue"
{| id="TABLE3" class="SpreadSheet blue"
|- class="even"
|- class="even"
| class=" " |
| class="ssh1_f" |SUBSTITUTE
| Column1
| class=" " |
| class=" " | Column2
| class=" " |
|- class="odd"
|- class="odd"
| class=" " | Row1
| class="ssh1_f" |abc123abc123
| class=" " | Cost Price
| class=" " |
| class="sshl_f" | Selling Price
| class=" " |
|
|- class="even"
|- class="even"
| class=" " | Row2
| class="ssh1_f" |Cost Price
| class="sshl_f " |
| 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]