Text Functions

Return to the Index
CHAR | CLEAN | CODE | CONCATENATE | DOLLAR | EXACT | FIND | FIXED | LEFT | LEN | LOWER | MID | PROPER | REPLACE | REPT | RIGHT | SUBSTITUTE | T | TEXT | TRIM | UPPER | VALUE

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.