Text Functions in Calc

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 20:04, 21 October 2007 (edit)
Jgd (Talk | contribs)
(translation of the French page Fr:Fonctions de Texte dans Calc, needs polish)
← Previous diff		 Revision as of 23:39, 21 October 2007 (edit) (undo)
Lorinda (Talk | contribs)
(Just a little polish; thanks, Jacqueline!)
Next diff →
Line 1: Line 1:
{{fixme}} {{fixme}}
-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 gives some examples.<br>+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.<br>
-===Introdctory Remark===+===Introductory Remark===
-To modify a cell content, you can't apply to that cell a formula which refers to it.+To modify a cell's content, you can't apply a forumla to that cell.
<br> <br>
:'''Example'''<br> :'''Example'''<br>
Line 8: Line 8:
===SUBSTITUTE Function=== ===SUBSTITUTE Function===
-That function replaces a 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.+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'''<br> :'''Examples'''<br>
Line 20: Line 20:
===REPLACE Function=== ===REPLACE Function===
-That function replaces a string with another one too, but iit's 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.+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. '''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.
Line 28: Line 28:
===CONCATENATE Function=== ===CONCATENATE Function===
-That 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, yo need to split the string in two (or more, depending of it's length) parts, and concatenate the various parts.+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 et FIND Functions===+===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. 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=== ===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. <br> 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. <br>
-:'''Application''' : Finding the first word in cell A1. Enter +:'''Example''' : Finding the first word in cell A1. Enter
::=LEFT(A1;SEARCH(" ";A1)-1) ::=LEFT(A1;SEARCH(" ";A1)-1)
'''Note''' : If you enter =LEFT(A1;SEARCH(" ";A1)), you get the first word ''and'' the following space. '''Note''' : If you enter =LEFT(A1;SEARCH(" ";A1)), you get the first word ''and'' the following space.
===RIGHT Function=== ===RIGHT Function===
-That 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 it's length. To solve that problem, you need to use the SEARCH function with regular expressions.+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 The formula
Line 51: Line 51:
==Links in Neowiki== ==Links in Neowiki==
-You may look at [[:Parsing Names in NeoOffice Calc|this page]] to see an application of those 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]]

Revision as of 23:39, 21 October 2007

This article is not up to date, or it needs structural or stylistic changes to conform with the rest of this wiki.

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.


This article in other languages: Français
Retrieved from "https://neowiki.neooffice.org/index.php/Text_Functions_in_Calc"
Personal tools