String

  • ASC text with full-width katakana and ASCII characters converted to half-width
  • CHAR the CP1252 (Windows-1252) character for the code point x
  • CLEAN text with any non-printable characters removed
  • CODE the CP1252 (Windows-1252) code point for the character c
  • CONCAT the concatenation of the strings s1, s2,…
  • CONCATENATE the concatenation of the strings s1, s2,…
  • DOLLAR num formatted as currency
  • EXACT TRUE if string1 is exactly equal to string2
  • FIND first position of string1 in string2 following position start
  • FINDB first byte position of string1 in string2 following byte position start
  • FIXED formatted string representation of num
  • JIS text with half-width katakana and ASCII characters converted to full-width
  • LEFT the first num_chars characters of the string s
  • LEFTB the first characters of the string s comprising at most num_bytes bytes
  • LEN the number of characters of the string s
  • LENB the number of bytes in the string s
  • LOWER a lower-case version of the string text
  • MID the substring of the string s starting at position position consisting of length characters
  • MIDB the characters following the first start_pos bytes comprising at most num_bytes bytes
  • NUMBERVALUE numeric value of text
  • PROPER text with initial of each word capitalised
  • REPLACE string old with num characters starting at start replaced by new
  • REPLACEB string old with up to num bytes starting at start replaced by new
  • REPT num repetitions of string text
  • RIGHT the last num_chars characters of the string s
  • RIGHTB the last characters of the string s comprising at most num_bytes bytes
  • SEARCH the location of the search string within text after position start
  • SEARCHB the location of the search string within text after byte position start
  • SUBSTITUTE text with all occurrences of old replaced by new
  • T value if and only if value is text, otherwise empty
  • TEXT value as a string formatted as format
  • TEXTJOIN the concatenation of the strings s1, s2,… delimited by del
  • TRIM text with only single spaces between words
  • UNICHAR the Unicode character represented by the Unicode code point x
  • UNICODE the Unicode code point for the character c
  • UPPER an upper-case version of the string text
  • VALUE numeric value of text

ASC

ASC text with full-width katakana and ASCII characters converted to half-width

Synopsis

ASC(text)

Arguments

text: string

Description

ASC converts full-width katakana and ASCII characters to half-width equivalent characters, copying all others.

The distinction between half-width and full-width characters is described in http://www.unicode.org/reports/tr11/.

Note

While in obsolete encodings ASC used to translate between 2-byte and 1-byte characters, this is not the case in UTF-8.

Microsoft Excel Compatibility

For most strings, this function has the same effect as in Excel.

OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

See also

JIS.

CHAR

CHAR the CP1252 (Windows-1252) character for the code point x

Synopsis

CHAR(x)

Arguments

x: code point

Description

CHAR(x) returns the CP1252 (Windows-1252) character with code x.

x must be in the range 1 to 255.

CP1252 (Windows-1252) is also known as the "ANSI code page", but it is not an ANSI standard.

CP1252 (Windows-1252) is based on an early draft of ISO-8859-1, and contains all of its printable characters. It also contains all of ISO-8859-15's printable characters (but partially at different positions.)

This function is Excel compatible.

Note

In CP1252 (Windows-1252), 129, 141, 143, 144, and 157 do not have matching characters. For x from 1 to 255 except 129, 141, 143, 144, and 157 we have CODE(CHAR(x))=x.

See also

CODE.

CLEAN

CLEAN text with any non-printable characters removed

Synopsis

CLEAN(text)

Arguments

text: string

Description

CLEAN removes non-printable characters from its argument leaving only regular characters and white-space.

Microsoft Excel Compatibility

This function is Excel compatible.

CODE

CODE the CP1252 (Windows-1252) code point for the character c

Synopsis

CODE(c)

Arguments

c: character

Description

c must be a valid CP1252 (Windows-1252) character.

CP1252 (Windows-1252) is also known as the "ANSI code page", but it is not an ANSI standard.

CP1252 (Windows-1252) is based on an early draft of ISO-8859-1, and contains all of its printable characters (but partially at different positions.)

This function is Excel compatible.

Note

In CP1252 (Windows-1252), 129, 141, 143, 144, and 157 do not have matching characters. For x from 1 to 255 except 129, 141, 143, 144, and 157 we have CODE(CHAR(x))=x.

See also

CHAR.

CONCAT

CONCAT the concatenation of the strings s1, s2,…

Synopsis

CONCAT(s1,s2,…)

Arguments

s1: first string

s2: second string

Note

This function is identical to CONCATENATE

Microsoft Excel Compatibility

This function is Excel compatible.

See also

LEFT, MID, RIGHT.

CONCATENATE

CONCATENATE the concatenation of the strings s1, s2,…

Synopsis

CONCATENATE(s1,s2,…)

Arguments

s1: first string

s2: second string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

LEFT, MID, RIGHT.

DOLLAR

DOLLAR num formatted as currency

Synopsis

DOLLAR(num,decimals)

Arguments

num: number

decimals: decimals

Microsoft Excel Compatibility

This function is Excel compatible.

See also

FIXED, TEXT, VALUE.

EXACT

EXACT TRUE if string1 is exactly equal to string2

Synopsis

EXACT(string1,string2)

Arguments

string1: first string

string2: second string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

LEN, SEARCH, DELTA.

FIND

FIND first position of string1 in string2 following position start

Synopsis

FIND(string1,string2,start)

Arguments

string1: search string

string2: search field

start: starting position, defaults to 1

Note

This search is case-sensitive.

Microsoft Excel Compatibility

This function is Excel compatible.

See also

EXACT, LEN, MID, SEARCH.

FINDB

FINDB first byte position of string1 in string2 following byte position start

Synopsis

FINDB(string1,string2,start)

Arguments

string1: search string

string2: search field

start: starting byte position, defaults to 1

Note

This search is case-sensitive.

Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

See also

FIND, LEFTB, RIGHTB, LENB, LEFT, MID, RIGHT, LEN.

FIXED

FIXED formatted string representation of num

Synopsis

FIXED(num,decimals,no_commas)

Arguments

num: number

decimals: number of decimals

no_commas: TRUE if no thousand separators should be used, defaults to FALSE

Microsoft Excel Compatibility

This function is Excel compatible.

See also

TEXT, VALUE, DOLLAR.

JIS

JIS text with half-width katakana and ASCII characters converted to full-width

Synopsis

JIS(text)

Arguments

text: original text

Description

JIS converts half-width katakana and ASCII characters to full-width equivalent characters, copying all others.

The distinction between half-width and full-width characters is described in http://www.unicode.org/reports/tr11/.

Note

While in obsolete encodings JIS used to translate between 1-byte and 2-byte characters, this is not the case in UTF-8.

Microsoft Excel Compatibility

For most strings, this function has the same effect as in Excel.

OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

See also

ASC.

LEFT

LEFT the first num_chars characters of the string s

Synopsis

LEFT(s,num_chars)

Arguments

s: the string

num_chars: the number of characters to return (defaults to 1)

Note

If the string s is in a right-to-left script, the returned first characters are from the right of the string.

Microsoft Excel Compatibility

This function is Excel compatible.

OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

See also

MID, RIGHT, LEN, MIDB, RIGHTB, LENB.

LEFTB

LEFTB the first characters of the string s comprising at most num_bytes bytes

Synopsis

LEFTB(s,num_bytes)

Arguments

s: the string

num_bytes: the maximum number of bytes to return (defaults to 1)

Note

The semantics of this function is subject to change as various applications implement it. If the string is in a right-to-left script, the returned first characters are from the right of the string.

Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

See also

MIDB, RIGHTB, LENB, LEFT, MID, RIGHT, LEN.

LEN

LEN the number of characters of the string s

Synopsis

LEN(s)

Arguments

s: the string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

CHAR, CODE, LENB.

LENB

LENB the number of bytes in the string s

Synopsis

LENB(s)

Arguments

s: the string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

CHAR, CODE, LEN.

LOWER

LOWER a lower-case version of the string text

Synopsis

LOWER(text)

Arguments

text: string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

UPPER.

MID

MID the substring of the string s starting at position position consisting of length characters

Synopsis

MID(s,position,length)

Arguments

s: the string

position: the starting position

length: the number of characters to return

Microsoft Excel Compatibility

This function is Excel compatible.

OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

See also

LEFT, RIGHT, LEN, LEFTB, MIDB, RIGHTB, LENB.

MIDB

MIDB the characters following the first start_pos bytes comprising at most num_bytes bytes

Synopsis

MIDB(s,start_pos,num_bytes)

Arguments

s: the string

start_pos: the number of the byte with which to start (defaults to 1)

num_bytes: the maximum number of bytes to return (defaults to 1)

Note

The semantics of this function is subject to change as various applications implement it.

Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

See also

LEFTB, RIGHTB, LENB, LEFT, MID, RIGHT, LEN.

NUMBERVALUE

NUMBERVALUE numeric value of text

Synopsis

NUMBERVALUE(text,separator)

Arguments

text: string

separator: decimal separator

Note

If text does not look like a decimal number, NUMBERVALUE returns the value VALUE would return (ignoring the given separator).

OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

See also

VALUE.

PROPER

PROPER text with initial of each word capitalised

Synopsis

PROPER(text)

Arguments

text: string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

LOWER, UPPER.

REPLACE

REPLACE string old with num characters starting at start replaced by new

Synopsis

REPLACE(old,start,num,new)

Arguments

old: original text

start: starting position

num: number of characters to be replaced

new: replacement string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

MID, SEARCH, SUBSTITUTE, TRIM.

REPLACEB

REPLACEB string old with up to num bytes starting at start replaced by new

Synopsis

REPLACEB(old,start,num,new)

Arguments

old: original text

start: starting byte position

num: number of bytes to be replaced

new: replacement string

Description

REPLACEB replaces the string of valid unicode characters starting at the byte start and ending at start+num-1 with the string new.

Note

The semantics of this function is subject to change as various applications implement it.

Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

See also

MID, SEARCH, SUBSTITUTE, TRIM.

REPT

REPT num repetitions of string text

Synopsis

REPT(text,num)

Arguments

text: string

num: non-negative integer

Microsoft Excel Compatibility

This function is Excel compatible.

See also

CONCATENATE.

RIGHT

RIGHT the last num_chars characters of the string s

Synopsis

RIGHT(s,num_chars)

Arguments

s: the string

num_chars: the number of characters to return (defaults to 1)

Note

If the string s is in a right-to-left script, the returned last characters are from the left of the string.

Microsoft Excel Compatibility

This function is Excel compatible.

OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

See also

LEFT, MID, LEN, LEFTB, MIDB, RIGHTB, LENB.

RIGHTB

RIGHTB the last characters of the string s comprising at most num_bytes bytes

Synopsis

RIGHTB(s,num_bytes)

Arguments

s: the string

num_bytes: the maximum number of bytes to return (defaults to 1)

Note

The semantics of this function is subject to change as various applications implement it. If the string s is in a right-to-left script, the returned last characters are from the left of the string.

Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

See also

LEFTB, MIDB, LENB, LEFT, MID, RIGHT, LEN.

SEARCH

SEARCH the location of the search string within text after position start

Synopsis

SEARCH(search,text,start)

Arguments

search: search string

text: search field

start: starting position, defaults to 1

Description

search may contain wildcard characters (*) and question marks (?). A question mark matches any single character, and a wildcard matches any string including the empty string. To search for * or ?, precede the symbol with ~.

Note

This search is not case sensitive. If search is not found, SEARCH returns #VALUE! If start is less than one or it is greater than the length of text, SEARCH returns #VALUE!

Microsoft Excel Compatibility

This function is Excel compatible.

See also

FIND, SEARCHB.

SEARCHB

SEARCHB the location of the search string within text after byte position start

Synopsis

SEARCHB(search,text,start)

Arguments

search: search string

text: search field

start: starting byte position, defaults to 1

Description

search may contain wildcard characters (*) and question marks (?). A question mark matches any single character, and a wildcard matches any string including the empty string. To search for * or ?, precede the symbol with ~.

Note

This search is not case sensitive. If search is not found, SEARCHB returns #VALUE! If start is less than one or it is greater than the byte length of text, SEARCHB returns #VALUE! The semantics of this function is subject to change as various applications implement it.

Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

See also

FINDB, SEARCH.

SUBSTITUTE

SUBSTITUTE text with all occurrences of old replaced by new

Synopsis

SUBSTITUTE(text,old,new,num)

Arguments

text: original text

old: string to be replaced

new: replacement string

num: if num is specified and a number only the numth occurrence of old is replaced

Microsoft Excel Compatibility

This function is Excel compatible.

See also

REPLACE, TRIM.

T

T value if and only if value is text, otherwise empty

Synopsis

T(value)

Arguments

value: original value

Microsoft Excel Compatibility

This function is Excel compatible.

See also

CELL, N, VALUE.

TEXT

TEXT value as a string formatted as format

Synopsis

TEXT(value,format)

Arguments

value: value to be formatted

format: desired format

Microsoft Excel Compatibility

This function is Excel compatible.

See also

DOLLAR, FIXED, VALUE.

TEXTJOIN

TEXTJOIN the concatenation of the strings s1, s2,… delimited by del

Synopsis

TEXTJOIN(del,blank,s1,s2,…)

Arguments

del: delimiter

blank: ignore blanks

s1: first string

s2: second string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

CONCATENATE.

TRIM

TRIM text with only single spaces between words

Synopsis

TRIM(text)

Arguments

text: string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

CLEAN, MID, REPLACE, SUBSTITUTE.

UNICHAR

UNICHAR the Unicode character represented by the Unicode code point x

Synopsis

UNICHAR(x)

Arguments

x: Unicode code point

See also

CHAR, UNICODE, CODE.

UNICODE

UNICODE the Unicode code point for the character c

Synopsis

UNICODE(c)

Arguments

c: character

See also

UNICHAR, CODE, CHAR.

UPPER

UPPER an upper-case version of the string text

Synopsis

UPPER(text)

Arguments

text: string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

LOWER.

VALUE

VALUE numeric value of text

Synopsis

VALUE(text)

Arguments

text: string

Microsoft Excel Compatibility

This function is Excel compatible.

See also

DOLLAR, FIXED, TEXT.