Text Functions in Calc

From NeoWiki

---

Revision as of 20:04, 21 October 2007

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.

Contents 1 Introdctory Remark 2 SUBSTITUTE Function 3 REPLACE Function 4 CONCATENATE Function 5 SEARCH et FIND Functions 6 LEFT Function 7 RIGHT Function 8 Links in Neowiki

Introdctory Remark

To modify a cell content, you can't apply to that cell a formula which refers to it.

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

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.

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

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.

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

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.

SEARCH et 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.

Application : 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

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.

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 those functions.