TONUMBER

The TONUMBER function converts a text string of a specified format to a number.

Syntax:

TONUMBER ( single-character-text-expression
  [ , single-text-expression ] )

Meaning:
TONUMBER ( text_to_convert [ , number_format_string ] )
Returns:
A single character number item

TONUMBER returns the number that corresponds to the value specified by text_to_convert, which is in the format specified by number_format_string. If number_format_string is not specified, it will be assumed that text_to_convert is in ANSI decimal format (for example, "{L-####['.'##]}").

The number_format_string must conform to the number format strings as described in the "Format strings" topic.

Examples

  • TONUMBER(text_to_convert, "{L+'$'#','###}")

    L+'$' indicates the leading dollar sign is positive. That leading sign and the comma separators are removed when the text is converted to a number.

    Input String: $123,000,000

    Output: 123000000

  • TONUMBER(text_to_convert, "{####T-}")

    Four number signs are required for each whole number, regardless of the actual number of digits in the number.

    Input string: Output: Note:
    12345- -12345 The output becomes a negative number.
    67890 67890 No change occurs.
    345- -345 The output becomes a negative number.
  • TONUMBER(text_to_convert, "{####T+'K'-}")

    If an invalid character, such as an X, is encountered, nothing is returned.

    If a K is encountered, it is treated as a positive indicator.

    Input string: Output: Note:
    11212- -11212 The output becomes a negative number.
    67890X   The X is an invalid character. No number is returned.
    54354 54354 No change occurs.
    34567K 34567 The K is recognized as a positive sign. The character is removed and the number is returned as a positive.
    345- -345 The output becomes a negative number.
  • TONUMBER(text_to_convert, "{L-'('#','###T-')'}")

    The parentheses indicating a negative number are removed and replaced with a negative sign.

    Comma separators are removed when the text is converted to a number.

    Input string: Output: Note:
    (12,345) -12345 The output becomes a negative number. The comma separator is removed.
    67,890 67890 The comma separator is removed.
    (345) -345 The output becomes a negative number.
  • TONUMBER(text_to_convert, "{#[',']###['.'##5]T+'K'-}")

    The optional comma separators are removed, but the decimal points and decimal values are retained.

    Input string: Output: Note:
    54,345.098 54354.098 The comma separator is removed.
    67890.0X   The X is an invalid character. No number is returned.
    11213- -11213 The output becomes a negative number.
    34567K 34567 The K is recognized as a positive sign. The character is removed and the number is returned as a positive.
    345.1- -345.1 The output becomes a negative number.

Related functions

  • DATETONUMBER
  • FROMNUMBER
  • NUMBERTODATE
  • NUMBERTOTEXT

Feedback