Text Functions

Text functions perfom a variety of formatting, sorting, and parsing actions.  

CHAR

Returns the character for given ASCII value.

Syntax

CHAR( number )

Arguments

number An ASCII character value.

Example

CHAR( 65 ) returns "A"
CHAR( 65 + 32 ) returns "a".

CODE

Returns the ASCII value for the first character in the expression.

Syntax

CODE( text-expression )

Arguments

text-expression Text.  The function returns the ASCII value for the first character only.

Example

CODE ( "Andrew" ) returns 65

CLEAN

Connverts all non-printable characters in an expression to spaces.

Syntax

CLEAN( text-expression )

Arguments

text-expression Text to clean out non-printable characters.

Example

CLEAN(CHAR(7)&"zippy"&CHAR(7)) returns "zippy"

CHAR(7) is a non-printable character.

CONCAT

Concatenates a text-expressions into a single text value. Numbers are converted to text as needed.

Syntax

CONCAT( text-expression, {text-expression}, ... )

Arguments

text-expression Text to concatenate

Example

CONCAT( "Number: ", 100 ) returns Number: 100

DOLLAR

Concatenates a text-expressions into a single text value. Numbers are converted to text as needed.

Syntax

DOLLAR( number, {decimals} )

Arguments

number Number to convert to currency formatted text.
decimals Number of decimal places to show. (If omitted, the default is 2.)

Example

DOLLAR( 1000, 1 ) returns $1,000.0

EXACT

Compares two text strings.  If both are exact matches, the function returns TRUE.

Syntax

EXACT( text1, text2 )

Arguments

text1 First text string to compare.
text2 Second text string to compare.

Example

EXACT( "one", "One" ) returns FALSE
EXACT( "one", "one" ) returns TRUE

FIND

Locates text within a text string, then returns the offset position of the first character in the string.  The offset is expressed in the number of characters from the beginning of the searched text string.  The first character in the string is 1, the second is 2, and so forth.  This function is identical to the SEARCH function, except it IS case-sensitive.

Syntax

FIND( find-text, source-text, {start-offset} )

Arguments

find-text Text to find.
source-text Text to search
start-offset Offset position in the text string to begin the search. The first character in the string is 1, the second 2, and so forth.

Example

FIND ( "ss", "Mississippi" ) returns 3
FIND ( "ss", "Mississippi" , 5 ) returns 6

FIXED

Converts a number to text with a fixed decimal place.

Syntax

FIXED( number, {decimals}, {no-commas} )

Arguments

number Number to convert.
decimals Number of decimal places.  The default is 2.
no-commas If set to TRUE, commas are omitted from the number. Otherwise, the number displays commas.

Example

FIXED( 10000 ) returns 10,000.00
FIXED ( 1000, 3, TRUE ) returns 1000.000

LEFT

Extracts the left-most characters from a text string. 

Syntax

LEFT( text-expression, number )

Arguments

text-expression Text to evaluate. \
number Number of charcters to extract.

Example

LEFT( "Gobe Software", 7 ) returns "Gobe So"

LEN

Returns the number of characters in a text string.

Syntax

LEN( text-expression )

Arguments

text-expression Text to evaluate. \

Example

LEN( "Gobe Software" ) returns 13

LOWER

Converts a text string to lower case characters.

Syntax

LOWER( text-expression )

Arguments

text-expression Text to evaluate. \

Example

LOWER( "Gobe Software" ) returns "gobe software"

MID

Extracts the characters from a text string beginning at the offset value.  The first character in a string has an offset of 1, the second 2, and so forth. . 

Syntax

MID( text-expression, start-offset, num_chars )

Arguments

text-expression Text to extract a string from.
start-offset The offset location in the text expression.  Must be greater than or equal to 1.
number Total number of charcters to extract.

Example

MID( "Gobe Software",2,5 ) returns "be So"

PROPER

Capitalizes the first letter of each sentence in a text string. Periods determine a sentence.

Syntax

PROPER( text-expression )

Arguments

text-expression Text to evaluate. \

Example

PROPER( "the developers are coding today. fantastic!" ) returns "The developers are coding today. fantastic!"

REPLACE

Replaces characters in a text string beginning at the offset position.  The first character in the text string is always in offset position 1, the second is 2 and so forth.

Syntax

REPLACE( text-expression, start-offset, num-chars, new-text )

Arguments

text-expression Text to evaluate.
start-offset Starting character position where the replacement begins.
num-chars Total number of characters in the text to replace.
new-text The text to insert. 

Example

REPLACE( "Go be there.", 3, 2, "sit" ) returns "Go sit there."

REPT

Repeats a text-string a given number of times.

Syntax

REPT( text-expression, count )

Arguments

text-expression Text to repeat.
count Number of times to repeat the text.  Must be between 1 and 1000.

Example

REPT( "*", 20 ) returns ********************

RIGHT

Extracts the right-most characters from a text string. 

Syntax

RIGHT( text-expression, number )

Arguments

text-expression Text to evaluate. \
number Number of charcters to extract.

Example

RIGHT( "Gobe Software", 4 ) returns "ware"

SEARCH

Locates text within a text string, then returns the offset position of the first character in the string.  The offset is expressed in the number of characters from the beginning of the searched text string.  The first character in the string is 1, the second is 2, and so forth.  This function is identical to the FIND function, except it is NOT case-sensitive.

Syntax

SEARCH( find-text, source-text, start-offset )

Arguments

find-text Text to find.
source-text Text to search
start-offset Offset position in the text string to begin the search, the default is 0 (the beginning of the string). The first character in the string is 1, the second 2, and so forth.

Example

FIND ( "ss", "Mississippi" ) returns 3
FIND ( "ss", "Mississippi" , 5 ) returns 6

SUBSTITUTE

Swaps an instance of text in a string with new text. 

Syntax

SUBSTITUTE( text-expression, old-text, new-text, instance )

Arguments

text-expression Text to evaluate.
old-text The text to swap.
new-text The text to insert.
instance The instance of the old-text to swap.  Must be greater than 0, if omitted the function assumes (the first instance of the text).

Example

SUBSTITUTE( "The old dog is old", "old", "blue", 2 ) returns "The old dog is blue"

T

Converts an expression to text.  Automatically converts arguments to functions as needed.  This function can return a number as text to overlap the number into adjacent cells. 

Syntax

T( expression )

Arguments

expression Text or number to convert to text format.

Example

T(100) returns 100

TEXT

Formats an expression using a format template.  The template must use the format conventions defined for Gobe Productive.

Syntax

TEXT( expression, format-text )

Arguments

expression Text or number to convert to text format.
format-text The format template. Must use the standard Gobe Productive format conventions (see below)

Format Conventions

Character Description
# Displays only significant digits.  Insignificant zeros are omitted. For example, in the format #.###, 0.010 would be displayed .01.
0 Displays digits regardless of significance.  For example, in the format 0.000, 0.010 would be displayed as 0.010
? Adds a space for insignificant zeros on either side of a decimal point.  This is used to ensure decimal points align correctly and that fractions appear correctly formatted. For example, the format # ???/??? would display 1.5 as 1--1/2-- where the dashes represent hard-spaces.
$ Dollar sign.  Used for currency values.
% Percentage sign.  Used for percentage formats. 
(  ) Parenthesis.  Used in place of negative sign.  In the format (0.00), -1.55 would appear as (1.55)
[Red] Indicates that the value is red when negative. In the format [Red](#.##), -1.50 would appear as (1.5).
E Scientific notation indicator. In the format 0.00E-00, 0.000000000005 would appear as 5.00E-12.

Example

TEXT( 1000, "#,###.0;-#,###.0" ) returns the text 1,000.0

TRIM

Trims all the spaces from a text string.

Syntax

TRIM( text-expression )

Arguments

text-expression Text or number to trim.

Example

VALUE( "Go be Productive" ) returns  "GobeProductive".

UPPER

Converts a text string to upper case characters.

Syntax

UPPER( text-expression )

Arguments

text-expression Text to evaluate. \

Example

UPPER( "Gobe Software" ) returns "GOBE SOFTWARE"

VALUE

Converts an expression to a number format, if possible.  Automatically converts arguments to functions as needed.

Syntax

VALUE( expression )

Arguments

expression Text or number to convert to number format.

Example

VALUE( "100"+".50" ) returns the value 100.5.


Copyright © 1999, Gobe Software, Inc