TOKENSUB Function

This function is used to return a formatted value containing predefined components.

Syntax

TOKENSUB("Format",Arguments)

Where Format defines the output content and specifies arguments as needed.

Argument specifiers are built as %[argument index]specifier. The Argument Index number calls the argument from the list following the formatted string. If no Argument Index number is specified, the function will read the arguments in order. See Example 1 for more information about this.

The specifier defines the type and the interpretation of the value of the argument. The value of the specifier can be any of the following.

Specifier

Output

Output if no argument is found

n

Numbers

0

s

Strings

""

d

Short date format

""

D

Long date format

""

%

Percentage

%

Following this structure to the argument specifier, %3d will locate the third entry in the argument list and attempt to return the value as a short date formatted string.

The argument list follows the Format and are separated by commas. Arguments can be a cell reference, a function such as SYSDATE(), or an ordinary string or value.

Example 1

TOKENSUB("This is %s generated on %d","sample text", SYSDATE())

Returns This is sample text generated on 10/04/2008

Since no argument index numbers are specified, the arguments must be listed in the order they are to appear in the string.

Example 2

TOKENSUB(C1, C2, C3) where

  • Cell C1 contains the value "%s %d <%2D>"

  • Cell C2 contains the string value "Today's date is"

  • Cell C3 contains the Julian date value "39487"

Returns Today's date is 10/04/2008 <April-10-08>

The cell references will be substituted, making the actual equation TOKENSUB("%s %2d <%2D>", "Today's date is", 39487). The function presents the first argument as a string, calls the second argument in short date format and then again in long date format, converting the Julian date value into a legible date.

Note:

  • Unsupported specifiers will appear in the resulting string.

    For example, TOKENSUB("This is a %B","test") will return the string This is a B.

  • Trailing zeroes are truncated in the resulting string.

    For example, TOKENSUB("I have %n dollars","65.00") will return the string I have 65 dollars.

  • The TOKENSUB function can process a list of 20 arguments at this time. If the list is longer than 20, the 21st and all subsequent arguments will be ignored.

Related Topics