Gobe Productive User's Guide - Text Functions

- Working in the spreadsheet environment
- Formatting cells
- Row & column sizes
- Number formats
- Insert, Delete, Move & Go to Cells
- Locking cells
- Filling cells
- Sorting cells
- Calculating cells & Dependent cells
- Formulas & functions
- Function reference
- Creating a chart
- Working with charts
- Styles
- Named selections
- Display options
- Spell checking
- Writing tools
- Views & layers
- Working with spreadsheet frames

# Text Functions

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.

## 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