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 — text with full-width katakana and ASCII characters converted to half-width

ASC(text)

text: string

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/.

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

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

This function is OpenFormula compatible.

JIS.

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

CHAR(x)

x: code point

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.

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.

CODE.

CLEAN — text with any non-printable characters removed

CLEAN(text)

text: string

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

This function is Excel compatible.

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

CODE(c)

c: character

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.

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.

CHAR.

CONCAT — the concatenation of the strings s1, s2,…

CONCAT(s1,s2,…)

s1: first string

s2: second string

This function is identical to CONCATENATE

This function is Excel compatible.

LEFT, MID, RIGHT.

CONCATENATE — the concatenation of the strings s1, s2,…

CONCATENATE(s1,s2,…)

s1: first string

s2: second string

This function is Excel compatible.

LEFT, MID, RIGHT.

DOLLAR — num formatted as currency

DOLLAR(num,decimals)

num: number

decimals: decimals

This function is Excel compatible.

FIXED, TEXT, VALUE.

EXACT — TRUE if string1 is exactly equal to string2

EXACT(string1,string2)

string1: first string

string2: second string

This function is Excel compatible.

LEN, SEARCH, DELTA.

FIND — first position of string1 in string2 following position start

FIND(string1,string2,start)

string1: search string

string2: search field

start: starting position, defaults to 1

This search is case-sensitive.

This function is Excel compatible.

EXACT, LEN, MID, SEARCH.

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

FINDB(string1,string2,start)

string1: search string

string2: search field

start: starting byte position, defaults to 1

This search is case-sensitive.

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

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

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

FIXED — formatted string representation of num

FIXED(num,decimals,no_commas)

num: number

decimals: number of decimals

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

This function is Excel compatible.

TEXT, VALUE, DOLLAR.

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

JIS(text)

text: original text

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/.

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

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

This function is OpenFormula compatible.

ASC.

LEFT — the first num_chars characters of the string s

LEFT(s,num_chars)

s: the string

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

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

This function is Excel compatible.

This function is OpenFormula compatible.

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

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

LEFTB(s,num_bytes)

s: the string

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

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.

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

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

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

LEN — the number of characters of the string s

LEN(s)

s: the string

This function is Excel compatible.

CHAR, CODE, LENB.

LENB — the number of bytes in the string s

LENB(s)

s: the string

This function is Excel compatible.

CHAR, CODE, LEN.

LOWER — a lower-case version of the string text

LOWER(text)

text: string

This function is Excel compatible.

UPPER.

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

MID(s,position,length)

s: the string

position: the starting position

length: the number of characters to return

This function is Excel compatible.

This function is OpenFormula compatible.

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

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

MIDB(s,start_pos,num_bytes)

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)

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

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

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

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

NUMBERVALUE — numeric value of text

NUMBERVALUE(text,separator)

text: string

separator: decimal separator

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

This function is OpenFormula compatible.

VALUE.

PROPER — text with initial of each word capitalised

PROPER(text)

text: string

This function is Excel compatible.

LOWER, UPPER.

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

REPLACE(old,start,num,new)

old: original text

start: starting position

num: number of characters to be replaced

new: replacement string

This function is Excel compatible.

MID, SEARCH, SUBSTITUTE, TRIM.

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

REPLACEB(old,start,num,new)

old: original text

start: starting byte position

num: number of bytes to be replaced

new: replacement string

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

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

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

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

MID, SEARCH, SUBSTITUTE, TRIM.

REPT — num repetitions of string text

REPT(text,num)

text: string

num: non-negative integer

This function is Excel compatible.

CONCATENATE.

RIGHT — the last num_chars characters of the string s

RIGHT(s,num_chars)

s: the string

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

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

This function is Excel compatible.

This function is OpenFormula compatible.

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

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

RIGHTB(s,num_bytes)

s: the string

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

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.

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

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

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

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

SEARCH(search,text,start)

search: search string

text: search field

start: starting position, defaults to 1

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 ~.

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!

This function is Excel compatible.

FIND, SEARCHB.

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

SEARCHB(search,text,start)

search: search string

text: search field

start: starting byte position, defaults to 1

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.

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

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

FINDB, SEARCH.

SUBSTITUTE — text with all occurrences of old replaced by new

SUBSTITUTE(text,old,new,num)

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

This function is Excel compatible.

REPLACE, TRIM.

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

T(value)

value: original value

This function is Excel compatible.

CELL, N, VALUE.

TEXT — value as a string formatted as format

TEXT(value,format)

value: value to be formatted

format: desired format

This function is Excel compatible.

DOLLAR, FIXED, VALUE.

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

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

del: delimiter

blank: ignore blanks

s1: first string

s2: second string

This function is Excel compatible.

CONCATENATE.

TRIM — text with only single spaces between words

TRIM(text)

text: string

This function is Excel compatible.

CLEAN, MID, REPLACE, SUBSTITUTE.

UNICHAR — the Unicode character represented by the Unicode code point x

UNICHAR(x)

x: Unicode code point

CHAR, UNICODE, CODE.

UNICODE — the Unicode code point for the character c

UNICODE(c)

c: character

UNICHAR, CODE, CHAR.

UPPER — an upper-case version of the string text

UPPER(text)

text: string

This function is Excel compatible.

LOWER.

VALUE — numeric value of text

VALUE(text)

text: string

This function is Excel compatible.

DOLLAR, FIXED, TEXT.

String

ASC

Synopsis

Arguments

Description

Note

Microsoft Excel Compatibility

OpenDocument Format (ODF) Compatibility

See also

CHAR

Synopsis

Arguments

Description

Note

See also

CLEAN

Synopsis

Arguments

Description

Microsoft Excel Compatibility

CODE

Synopsis

Arguments

Description

Note

See also

CONCAT

Synopsis

Arguments

Note

Microsoft Excel Compatibility

See also

CONCATENATE

Synopsis

Arguments

Microsoft Excel Compatibility

See also

DOLLAR

Synopsis

Arguments

Microsoft Excel Compatibility

See also

EXACT

Synopsis

Arguments

Microsoft Excel Compatibility

See also

FIND

Synopsis

Arguments

Note

Microsoft Excel Compatibility

See also

FINDB

Synopsis

Arguments

Note

Microsoft Excel Compatibility

OpenDocument Format (ODF) Compatibility

See also

FIXED

Synopsis

Arguments

Microsoft Excel Compatibility

See also

JIS

Synopsis

Arguments

Description

Note

Microsoft Excel Compatibility

OpenDocument Format (ODF) Compatibility

See also

LEFT

Synopsis

Arguments

Note

Microsoft Excel Compatibility

OpenDocument Format (ODF) Compatibility

See also