|
|
Text Functions in Calc
From NeoWiki
| Revision as of 23:39, 21 October 2007 (edit) Lorinda (Talk | contribs) m (remove Fixme) ← Previous diff |
Current revision (01:42, 22 October 2007) (edit) (undo) Sardisson (Talk | contribs) (→Links in NeoWiki - enforce house style on "NeoWiki" ;)) |
||
| Line 49: | Line 49: | ||
| ::=RIGHT(A1;LEN(A1)-SEARCH(" [a-z0-9]+$";A1)) | ::=RIGHT(A1;LEN(A1)-SEARCH(" [a-z0-9]+$";A1)) | ||
| - | ==Links in | + | ==Links in NeoWiki== |
| You may look at [[:Parsing Names in NeoOffice Calc|this page]] to see an application of these functions. | You may look at [[:Parsing Names in NeoOffice Calc|this page]] to see an application of these functions. | ||
| {{botlangbarEN|[[Fr:Fonctions de Texte dans Calc|Français]]}} | {{botlangbarEN|[[Fr:Fonctions de Texte dans Calc|Français]]}} | ||
| [[Category:Tips and Hints]][[Category:NeoOffice]] | [[Category:Tips and Hints]][[Category:NeoOffice]] | ||
Current revision
Some text functions are well described in NeoOffice help (choose Help > NeoOffice Help > NeoOffice Calc, then type TEXT functions in the search field). However some others are more difficult to use. This article provides some examples.
Contents |
Introductory Remark
To modify a cell's content, you can't apply a forumla to that cell.
- Example
If you want to capitalize the content of the cell A1, you can't select A1 and type =UPPER(A1) in the formula bar. You need to select an intermediate cell, e.g. B1, enter =UPPER(A1) in the formula bar, and press Return. Then you must select B1, copy it (Cmd-C or cut it Cmd-X), click in A1, then go in the Edit menu, choose Paste Special…, or press the Shift-Cmd-V keys. In the dialog which opens, uncheck Paste all and Formulas .
SUBSTITUTE Function
This function replaces a (text) string with another one; it uses the LEN function which returns the length of a string in a cell. It takes four parameters: the text that is to be modified, the text that is to be replaced, the replacement text and the occurrence that is to be replaced (optional). if you don't specify any occurrence, all the occurrences are replaced.
- Examples
The cell A1 contains the text "sessions of June 2000, September 2000 and June 2002".
- =SUBSTITUTE(A1;"2000";"2001") replaces "sessions of June 2000, September 2000 and June 2002" with "sessions of June 2001, September 2001 and June 2002".
- =SUBSTITUTE(A1;"2000";"2001";2) replaces "sessions of June 2000, September 2000 and June 2002" with "sessions of June 2000, September 2001 and June 2002".
Counting the number of occurrences of a character in a cell, e.g. of the letter "a" in cell A1:
- =LEN(A1)-LEN(SUBSTITUTE(A1;"a";""))
You get only the number of the lowercase occurrences of "a". If you want to get the number of occurrences of "a" and "A", you have to enter:
- =LEN(A1)-LEN(SUBSTITUTE(A1;"a";""))+(LEN(A1)-LEN(SUBSTITUTE(A1;"A";"")))
REPLACE Function
This function replaces a string with another one too, but its paramaters are different from those of the SUBSTITUTE function. They are: the text that is to be modified, the position where the replacement will begin, the number of characters to be replaced and the replacement text.
Note : if you apply the SUBSTITUTE or REPLACE functions to numbers, the result will be in text format. You will need to convert it to a number by using the VALUE function.
- =VALUE(REPLACE(1115693;2;2;365))
returns the number 13655693.
CONCATENATE Function
This function links two ore more strings together. It's easy to use, but be aware that if one of the strings is too long (starting from 243 characters), you get an error message. In that case, you need to split the string in two (or more, depending on its length) parts, and concatenate the various parts.
SEARCH and FIND Functions
Those two functions find a string in a cell content. They differ in two points: SEARCH is not case sensitive and supports regular expressions (see the RIGHT Function section below), FIND is case sensitive but doesn't support regular expressions.
LEFT Function
That function returns the first character or characters of a text, taking that text and the number of characters which are to be returned as parameters.
- Example : Finding the first word in cell A1. Enter
- =LEFT(A1;SEARCH(" ";A1)-1)
Note : If you enter =LEFT(A1;SEARCH(" ";A1)), you get the first word and the following space.
RIGHT Function
This function returns the last character or characters of a text, taking that text and the number of rightmost characters as parameters. It's a bit tricky to use, because the number of rightmost characters is not always easy to get. For example, if you want to find the last word of a text, you generally don't know its length. To solve that problem, you need to use the SEARCH function with regular expressions.
The formula
- =RIGHT(A1;LEN(A1)-SEARCH(" [a-z]+$";A1))
returns the last word of the cell. Don't forget the quotes " and the space before [a-z]+$. If you need to take numbers into account, you have to enter:
- =RIGHT(A1;LEN(A1)-SEARCH(" [a-z0-9]+$";A1))
Links in NeoWiki
You may look at this page to see an application of these functions.
