Difference between revisions of "Manuals/calci/REPLACE"
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">'''REPLACE'''</font></font></font><font color="#4...") |
|||
| Line 1: | Line 1: | ||
| − | + | =REPLACE(otext, snum, noc, ntext)= | |
| − | < | + | *where <math>otext</math> is the original or old text |
| + | *<math>snum</math> is the position of character in old text | ||
| + | *<math>noc</math> is the number of characters | ||
| + | *<math>ntext</math> is the new text that replaces the characters in old text. | ||
| − | + | REPLACE() replaces a part of the text string with a different text string. | |
| − | + | == Description == | |
| − | + | REPLACE(otext, snum, noc, ntext) | |
| − | + | For Example, | |
| − | + | REPLACE("WELCOME",4,4,"-COME") returns ''WEL-COME'' | |
| − | |||
| − | |||
| − | + | In above example, original text is replaced with new text from 4th character to next 4 characters. | |
| − | < | + | *<math>otext</math> and <math>ntext</math> should be enclosed in quotes. |
| + | *<math>snum</math> and <math>noc</math> should be positive numbers. | ||
| + | *If <math>snum</math> and <math>noc</math> are non-integers, they are truncated. | ||
| + | *If <math>snum</math> is > the length of string, Calci assumes it to be '0'(zero) and new text is appended before old text. | ||
| + | *If any arguments are invalid, Calci displays an #ERROR message. | ||
| − | + | == Examples == | |
| − | < | + | <div id="6SpaceContent" class="zcontent" align="left"> |
| − | + | {| id="TABLE3" class="SpreadSheet blue" | |
| + | |- class="even" | ||
| + | | class="ssh1_f" |ColumnA | ||
| + | | class=" " |ColumnB | ||
| + | | class=" " |ColumnC | ||
| − | + | |- class="odd" | |
| − | - | + | | class="ssh1_f" |123456 |
| − | + | | class=" " | | |
| + | | class=" " | | ||
| − | |||
| − | + | |- class="even" | |
| − | + | | class="ssh1_f" |replace | |
| − | + | | class=" " | | |
| + | | class=" " | | ||
| − | |||
| − | + | |- class="odd" | |
| + | | class="ssh1_f" |ABCDEF | ||
| + | | class=" " | | ||
| + | | class=" " | | ||
| − | |||
| − | + | |- class="even" | |
| − | - | + | | class="ssh1_f" |Mango |
| − | + | | class=" " | | |
| − | + | | class=" " | | |
| − | + | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- class="odd" | |- class="odd" | ||
| − | | class=" " | | + | | class="ssh1_f" | |
| − | + | | class=" " | | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | | class=" " | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | | | ||
| class=" " | | | class=" " | | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|} | |} | ||
| − | + | =REPLACE(A1,2,1,"two") : Replaces second character from old text with "two". Returns '''1two3456''' as output. | |
| − | + | =REPLACE(A2,3,5,"PLACE") :Replaces 5 characters from old text starting from 3rd position with upper case "PLACE". Returns '''rePLACE''' as output. | |
| + | =REPLACE(A3,6,1,"fghijk") : Replaces 6th character from old text with lower case "fghijk". Returns '''ABCDEfghijk''' as output. | ||
| + | =REPLACE(A4,8,1,"Apple ") : Appends "Apple " before old text as snum is greater than length of old text. Returns '''Apple Mango''' as output. | ||
| + | |||
| + | == See Also == | ||
| + | |||
| + | *[[Manuals/calci/REPLACEB | REPLACEB]] | ||
| + | *[[Manuals/calci/SUBSTITUTE | SUBSTITUTE]] | ||
| + | *[[Manuals/calci/REPT | REPT]] | ||
| + | |||
| + | ==References== | ||
| + | |||
| + | *[http://en.wikipedia.org/wiki/Autocorrection Find and Replace] | ||
Revision as of 07:19, 28 December 2013
REPLACE(otext, snum, noc, ntext)
- where is the original or old text
- is the position of character in old text
- is the number of characters
- is the new text that replaces the characters in old text.
is the original or old text
is the position of character in old text
is the number of characters
is the new text that replaces the characters in old text.REPLACE() replaces a part of the text string with a different text string.
Description
REPLACE(otext, snum, noc, ntext)
For Example,
REPLACE("WELCOME",4,4,"-COME") returns WEL-COME
In above example, original text is replaced with new text from 4th character to next 4 characters.
- and should be enclosed in quotes.
- and should be positive numbers.
- If and are non-integers, they are truncated.
- If is > the length of string, Calci assumes it to be '0'(zero) and new text is appended before old text.
- If any arguments are invalid, Calci displays an #ERROR message.
Examples
| ColumnA | ColumnB | ColumnC |
| 123456 |
| |
| replace |
| |
| ABCDEF |
| |
| Mango |
| |
=REPLACE(A1,2,1,"two") : Replaces second character from old text with "two". Returns 1two3456 as output. =REPLACE(A2,3,5,"PLACE") :Replaces 5 characters from old text starting from 3rd position with upper case "PLACE". Returns rePLACE as output. =REPLACE(A3,6,1,"fghijk") : Replaces 6th character from old text with lower case "fghijk". Returns ABCDEfghijk as output. =REPLACE(A4,8,1,"Apple ") : Appends "Apple " before old text as snum is greater than length of old text. Returns Apple Mango as output.