6. Text functions
Prev | Chapter 4. Predefined functions | Next

## 6. Text functions

### 6.1. char

    char(code)

Returns a string containing a single character, this character having specified Unicode code. Reports an error if argument code cannot be converted to a number in the 0,65535 range.

Example: char(65) = "A"

See also code.

### 6.2. code

    code(string)

Returns the Unicode code of the first character contained in specified string. Reports an error if argument string cannot be converted to a non-empty string.

Example: code("ABC") = 65

See also char.

### 6.3. text

    text(number_or_date, format?, locale?)

Converts its number or date/time argument to a string.

If the argument is a number and number format format is not specified, a standard, non-localized, format is used.

If the argument is a date/time and date/time format format is not specified, a standard, non-localized, format is used.

If a format is specified, argument locale may be used to specify the locale of this format. Without locale argument locale, this format is interpreted using the current language of the XML document (typically specified using the standard `xml:lang` attribute, but this can be configured).

Examples, (assume that the language of the XML document being edited is "`en-US`"):

- text(PI()) = 3.141592653589793

- text(PI(), "0.00") = 3.14

- text(PI(), "") = 3.142 ("" is a shorthand notation for the default format)

- text(PI(), "0.00", "fr") = 3,14

- text(PI(), "", "fr") = 3,142

- text(today()) = 2004-10-12Z

- text(today(), "MMMM dd, yyyy") = October 13, 2004

- text(today(), "") = 10/13/04 ("" is a shorthand notation for the default format)

- text(today(), "MMMM dd, yyyy", "fr") = octobre 13, 2004

- text(today(), "", "fr") = 13/10/04

- text(now()) = 2004-10-13T15:51:59.321Z (today() returns a date. now() returns a date+time)

- text(now(), "") = 10/13/04 5:53 PM

- text(now(), "dd/MM/yyyy HH:mm:ss", "fr-CA") = 13/10/2004 17:53:18

Number formats are explained in the following document http://java.sun.com/j2se/1.4.2/docs/api/java/text/DecimalFormat.html.

Date/time formats are explained in the following document http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html.

Locales are specified using a standard 2-letter language code, optionally followed by a dash and a standard 2-letter country code. Examples: en, en-US, de, de-CH, etc.

### 6.4. mid

    mid(text, from, count)

Returns the substring of text containing count characters, starting at character #from. First character is character #1.

Example: mid("very long", 5, 4) = "long"

See also left, right.

### 6.5. left

    left(text, count)

Returns the substring of text containing the first count characters.

Example: left("very long", 4) = "very"

See also mid, right.

### 6.6. right

    right(text, count)

Returns the substring of text containing the last count characters.

Example: right("very long", 4) = "long"

See also left, mid.

### 6.7. trim

    trim(text)

Removes leading and trailing whitespace from text.

### 6.8. lower

    lower(text)

Returns text converted to lower case.

See also upper.

### 6.9. upper

    upper(text)

Returns text converted to upper case.

See also lower.

### 6.10. len

    len(text)

Returns the number of characters contained in text.

### 6.11. substitute

    substitute(text, old_text, new_text, occurrence?)

Returns text after substituting in it occurrences of substring old_text by new_text. If occurrence is specified, only occurrence #occurrence is substituted (first occurrence is occurrence #1). Otherwise all occurrences of old_text are substituted.

Examples:

- substitute("Element wordasword", "word", "term") = "Element termasterm"

- substitute("Element wordasword", "word", "term", 2) = "Element wordasterm"

- substitute("Element wordasword", "word", "not found") = "Element wordasword"

See also replace, find, search.

### 6.12. replace

    replace(text, from, count, new_text)

Returns text after replacing substring containing count characters and starting at character #from by new_text. First character of a string is character #1.

Example: replace("one two three", 4, 3, "2") = "one 2 three"

See also substitute, find, search.

### 6.13. find

    find(what, text, from?)

Returns the index of first occurrence of what found in text. Unless from is specified, search is started at character 1. (First character of a string is character #1.)

Reports an error if what is not found.

Unlike with search, what cannot contain wildcards and the lookup is case-sensitive.

Examples:

- find("A", "A rainy day.") = 1

- find("a", "A rainy day.") = 4

- find("A", "A rainy day.", 2) = ERROR

- find("a", "A rainy day.", 2) = 4

### 6.14. search

    search(what, text, from?)

Returns the index of first occurrence of what found in text. Unless from is specified, search is started at character 1. (First character of a string is character #1.)

Reports an error if what is not found.

Unlike with find, what can contain wildcards and the lookup is case-insensitive.

- Wildcard "*" matches any number of characters.

- Wildcard "?" matches a single character.

- Use "~?" and "~*" if you search a substring containing characters "?" and "*".

Examples:

- search("a", "A rainy day.") = 1

- search("a", "A rainy day.", 2) = 4

- search("It's", "A rainy day.") = ERROR

- search("a*y", "A rainy day.") = 4

- search("~*-tuple", "1-tuple 2-tuple *-tuple") = 17

Prev | Up | Next
5. Statistical functions | Home | Chapter 5. Defining custom spreadsheet functions