Text Functions

CHAR

Returns the character corresponding to the specified number using the character set from the default locale. Syntax: CHAR(number) Number is any integer from 1 to 255. Example: char(66) equals "B" char(code("h")) equals "h"

CLEAN

Returns the string after removing all non-printable characters. Syntax: CLEAN(text) Text is any string value. Example: clean(concatenate("hi", char(10), "nd")) equals "hind"

CODE

Returns the numeric code for the first character in the string. Syntax: CODE(text) Text is any text string. Example: code("hi") equals 104 code(char(67)) equals 67

CONCATENATE

Returns the concatenation of all of the arguments. Syntax: CONCATENATE(text1, text2, ...) text1, text2, ... is one or more strings. Example: concatenate("hello", " ", "world") equals "hello world"

DOLLAR

Converts a number to a string using the default currency format for the default locale. Syntax: DOLLAR(number) Number is any real number. Example: dollar(5.97) equals "$5.97"

EXACT

Returns true if the two strings are exactly the same. A case sensitive comparison is performed. Syntax: EXACT(text1, text2) text1, text2 are the two strings to compare. Example: exact("hi", "hi") equals true

FIND

Returns the position of the first string within the second string. FIND is case-sensitive. The value 0 is return if the string is not found Syntax: FIND(text_to_find, within_text, start) text_to_find is the text to find. within_text is the text to search inside. The returned position will be a 1-base index into this string. start is the 1-based position in the string, within_text, to start the search. If omitted the search starts at the beginning Example: find("l", "hello") equals 3 find("l", "hello", find("l", "hello"), 1) equals 4

FIXED

Returns a number formated to a text string using the default NumberFormat. Syntax: FIXED(number, digits, no_commas) Number is any real number. Digits is any integer. If omitted then 2 is assumed. If 0, then the number is rounded to the closest integer. If > 0, then the number is rounded to the given decimal place. If < 0, then the number is rounded to the given place left of the decimal. No_commas if true prevents grouping character (comma in US locale) from being added. If omitted or false, the grouping character is added. Example: fixed(1234.5678, -1) equals "1,230"

LEFT

Returns the specified number of characters at the beginning of the string. Syntax: LEFT(text) Text is any text string. Example: left("hello world", 2) equals "he"

LEN

Returns the length of a text string. Syntax: LEN(text) Text is any text string. Example: len("hello world") equals 11

LOWER

Returns the text string converted to lower case characters. Syntax: LOWER(text) Text is any text string. Example: lower("HoWdY") equals "howdy"

MID

Returns the specified sub-string of a text string. Syntax: MID(text) Text is any text string. Example: mid("hello", 3, 2) equals "ll"

PROPER

Returns the text string converted to lower case characters and then capitalizes the first character of each word. Syntax: PROPER(text) Text is any text string. Example: proper("hello world") equals "Hello World"

REPLACE

Replaces the specified number of characters with the given string. Syntax: REPLACE(text, start, length, withStr) Text is the text string to receive the changes. start is the 1-based position in the text string to start the replacement. length is the number of characters in the text string to be removed. withStr is the new string to replace the removed characters with. Example: replace("hi2", 3, 1, " there") equals "hi there"

REPT

Returns a string repeated a specified number of times. Syntax: REPT(text) Text is any text string. Example: rept("*", 3) equals "***"

RIGHT

Returns the specified number of characters at the ending of the string. Syntax: RIGHT(text) Text is any text string. Example: right("hello world", 3) equals "rld"

SEARCH

Returns the position of the first string within the second string. SEARCH is case-insensitive. The value 0 is return if the string is not found Syntax: SEARCH(text_to_find, within_text, start) text_to_find is the text to find. within_text is the text to search inside. The returned position will be a 1-base index into this string. start is the 1-based position in the string, within_text, to start the search. If omitted the search starts at the beginning Example: search("l", "hello") equals 3

SUBSTITUTE

Finds and replaces one or all occurrences of a substring within a text string. Syntax: SUBSTITUTE(text, replaceStr, withStr, occurrenceNum) Text is the text string to receive the changes. replaceStr is the text string to be found and replaced. withStr is the new string to replace the removed string with. occurrenceNum is the occurrenc of replaceString to actually replace. If omitted then all occurrences will be replaced. Example: substitute("ababab", "ab", "-", 4) equals "ababab"

T

Returns the text of a given value. If the value is of type string then it is returned. Otherwise, a "" is returned. Syntax: T(value) Value is any value, cell reference, or expression. Example: t("hi") equals "hi" t(3) equals ""

TEXT

Converts a value to text using the specified number format. Syntax: TEXT(value, format) Value is any value, cell reference, or expression. Format is the number format to use. Example: text(2.715, "$0.00") equals "$2.72"

TRIM

Removes all spaces from the beginning and ending of the text string. All multiple spaces are also replaced by a single space. Syntax: TRIM(text) Text is any text string. Example: trim(" 2003 Sales ") equals "2003 Sales"

UPPER

Returns the text string converted to upper case characters. Syntax: UPPER(text) Text is any text string. Example: upper("hi") equals "HI"

VALUE

Converts a text string to a number. Syntax: VALUE(text) Text is any text string representing a number. Example: value("3.5") equals 3.5

Copyright 2003-2005 Diamond Edge, Inc. All rights reserved.