|
![]() |
|
CHAR | FIND | PROPER | T |
CODE | FIXED | REPLACE | TEXT |
CLEAN | LEFT | REPT | TRIM |
CONCAT | LEN | RIGHT | UPPER |
DOLLAR | LOWER | SEARCH | VALUE |
EXACT | MID | SUBSTITUTE |
Text functions perfom a variety of formatting, sorting, and parsing actions.
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".
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
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.
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
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
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
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
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
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"
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
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"
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"
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!"
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."
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 ********************
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"
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
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"
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
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
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".
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"
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 |