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.

numberThe number of the character that you want.



CLEAN(text)
Returns a string corresponding to the input string with any unprintable characters removed.

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

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

numberThe amount that you want to format.
decimalsThe 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.

text1The first string to be compared.
text2The 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_textThe text you want to find.
within_textThe text to be searched.
start_numThe 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.

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

FALSEThe thousands separator is used.
TRUEThe 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.

textThe string of which you want the beginning.
num_charsThe 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.

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

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

textThe string of which you want the extract.
start_numThe starting position of characters to extract from the string.
num_charsThe 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.

textThe 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_textThe string you want to convert.
start_numThe starting position of the text to be removed.
num_charsThe number of characters to be removed.
new_textThe 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.

textThe string you want to repeat.
number_timesThe 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.

textThe string of which you want the end.
num_charsThe 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_textThe text you want to find.
within_textThe text to be searched.
start_numThe 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.

textThe string you want to convert.
old_textThe string to be removed from the original text.
new_textThe string to be inserted in the original text.
instance_numSpecifies 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.

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

valueThe number that you want to format.
format_textThe format string to use.



TRIM(text)
Returns a string consisting of the input string with any extra spaces removed.

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

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

textThe string that you want to convert to a number.