SpreadCE Help

Text functions

[ ] indicates optional parameters

CHAR(number)
CLEAN(text)
CODE(text)
CONCATENATE(text1, [text2, ...])
DOLLAR(number, [decimals])
EXACT(text1, text2)
FIND(find_text, within_text, [start_num])
FIXED(number, [decimals], [no_commas])
LEFT(text, [num_chars])
LEN(text)
LOWER(text)
MID(text, start_num, num_chars)
PROPER(text)
REPLACE(old_text, start_num, num_chars, new_text)
REPT(text, number_times)
RIGHT(text, [num_chars])
SEARCH(find_text, within_text, [start_num])
SUBSTITUTE(text, old_text, new_text, [instance_num])
T(value)
TEXT(value, format_text)
TRIM(text)
UPPER(text)
VALUE(text)

Text functions

CHAR(number)

Returns the character corresponding to the specified position in the character set.

number | The number of the character that you want.

CLEAN(text)

Returns a string corresponding to the input string with any unprintable characters removed.

text | The text from which you want to remove unprintable characters.

CODE(text)

Returns the position in the character set of the first character in the string.

text | The character of which you want the character code.

CONCATENATE(text1, [text2, ...])

Returns a string consisting of the input strings concatenated together.

text1, ... | The strings that you want to concatenate.

DOLLAR(number, [decimals])

Returns a string consisting of the input number rounded to the specified number of decimal places and converted to currency format.

number | The amount that you want to format.
decimals | The number of decimal places required. If this parameter is omitted it defaults to 2.

EXACT(text1, text2)

Returns TRUE if the input strings are exactly the same, otherwise FALSE.

text1 | The first string to be compared.
text2 | The second string to be compared.

FIND(find_text, within_text, [start_num])

Returns a number corresponding to the position of one text string within another (case-sensitive).

find_text | The text you want to find.
within_text | The text to be searched.
start_num | The position at which to start searching. If this parameter is omitted it defaults to 1.

FIXED(number, [decimals], [no_commas])

Returns a string consisting of the input number rounded to the specified number of decimal places.

number | The number that you want to format.
decimals | The number of decimal places required. If this parameter is omitted it defaults to 2.
no_commas | Specifies whether to suppress the thousands separator in the formatted number. The possible values are:

FALSE | The thousands separator is used.
TRUE | The thousands separator is not used.

| If this parameter is omitted it defaults to FALSE.

LEFT(text, [num_chars])

Returns a string consisting of the specified number of characters from the beginning of the input string.

text | The string of which you want the beginning.
num_chars | The number of characters to extract from the string. If this parameter is omitted it defaults to 1.

LEN(text)

Returns the length of the input string.

text | The string of which you want the length.

LOWER(text)

Returns a string which is the input string with all of the characters converted to lower case.

text | The string you want to convert.

MID(text, start_num, num_chars)

Returns a string consisting of the specified number of characters from the specified position of the input string.

text | The string of which you want the extract.
start_num | The starting position of characters to extract from the string.
num_chars | The number of characters to extract from the string.

PROPER(text)

Returns a string which is the input string with the first letter of each word converted to upper case and all of the other characters converted to lower case.

text | The string you want to convert.

REPLACE(old_text, start_num, num_chars, new_text)

Returns a string which is the input string with a specified portion replaced.

old_text | The string you want to convert.
start_num | The starting position of the text to be removed.
num_chars | The number of characters to be removed.
new_text | The text to be inserted into the string.

REPT(text, number_times)

Returns a string which is the input string repeated a specified number of times.

text | The string you want to repeat.
number_times | The number of times to repeat the string.

RIGHT(text, [num_chars])

Returns a string consisting of the specified number of characters from the end of the input string.

text | The string of which you want the end.
num_chars | The number of characters to extract from the string. If this parameter is omitted it defaults to 1.

SEARCH(find_text, within_text, [start_num])

Returns a number corresponding to the position of one text string within another (not case-sensitive).

find_text | The text you want to find.
within_text | The text to be searched.
start_num | The position at which to start searching. If this parameter is omitted it defaults to 1.

SUBSTITUTE(text, old_text, new_text, [instance_num])

Returns a string which is the input string with specified text replaced.

text | The string you want to convert.
old_text | The string to be removed from the original text.
new_text | The string to be inserted in the original text.
instance_num | Specifies which occurrence of old_text is to be replaced. If this parameter is omitted then all occurrences are replaced.

T(value)

Returns a string which is the input value if it is text, otherwise an empty string.

value | The value you want if it is a string.

TEXT(value, format_text)

Returns a string consisting of the input number formatted using the specified format string.

value | The number that you want to format.
format_text | The format string to use.

TRIM(text)

Returns a string consisting of the input string with any extra spaces removed.

text | The string that you want to format.

UPPER(text)

Returns a string which is the input string with all of the characters converted to upper case.

text | The string that you want to convert.

VALUE(text)

Returns a number which corresponds to the value in the input string. You should not need to use this function, as values are automatically converted from text to numeric when required in formulas.

text | The string that you want to convert to a number.