Skip to main content

Conversion Functions

A conversion function converts one data type or format to another data type or format. Use a conversion function to convert numbers to strings or strings to numbers. Conversion functions can be used with String and Number data types.

BinToInt

BinToInt(s): Converts the binary string (s) to an integer. It is limited to 64 bits of precision and can be up to 64 bits in length.

Example

  • BinToInt("101010101") returns 341. If the string is exactly 32 or 64 characters long, it's treated as a signed number:

  • BinToInt("11111111111111111111111111111011") returns -5. The leading signs '+' and '-' take precedence over treating 32 and 64 characters as a signed number:

  • BinToInt("-111") returns -7.

  • BinToInt("+111") returns 7.

CharFromInt

CharFromInt(x): Returns the Unicode® character that matches the input number x.

Example

  • CharFromInt(66) returns B (U+0042 'Latin Capital Letter B').

  • CharFromInt(169) returns © (U+00A9 'copyright sign').

  • CharFromInt(1071) returns Я (U+042F 'Cyrillic capital letter YA').

  • CharFromInt(127944) returns 🏈 (U+1F3C8 'American football').

  • CharFromInt(0) returns [null] (U+000 'Null') because any integer that cannot be used to represent a character may give a null result.

  • CharFromInt(55300) returns [null] because any integer that does not currently represent a character will not be rendered by a normal font.

CharToInt

CharToInt(s): Returns the number that matches the input Unicode® character s.

Example

  • CharToInt("B") returns 66 (U+0042 'Latin Capital Letter B').

  • CharToInt("©") returns 169 (U+00A9 'copyright sign').

  • CharToInt("Я") returns 1071 (U+042F 'Cyrillic capital letter YA').

  • CharToInt() returns 127944 (U+1F3C8 'American football').

ConvertFromCodePage

ConvertFromCodePage(s, codePage): Translates text from a code page to Unicode. To learn more about code pages, go to the Code Pages documentation.

ConvertToCodePage

ConvertToCodePage(s, codePage): Translates text from Unicode® encoding to a specific code page. To learn more about code pages, visit the Code Pages documentation.

HexToNumber

HexToNumber(x): Converts a HEX string to a number (limited to 64 bits). If there are 64 bits, 16 hex characters, and the lead bit is set, then the result is negative.

Example

  • HexToNumber("7FFFFFFFFFFFFFFA") returns 9223372036854775802.

  • HexToNumber("FFFFFFFFFFFFFFFA") returns -6.

  • HexToNumber("FFFFFFFFFFFFFFFB") returns -5.

IntToBin

IntToBin(x): Converts x to a binary string.

IntToHex

IntToHex(x): Converts x to a hexadecimal string.

ToDegrees

ToDegrees(x): Converts a numeric radian value (x) to degrees via the (x)rad × 180/π calculation. Please note that x must be a numeric value and can’t include any radian symbol (rad).

Example

  • TODEGREES(0) returns 0.

  • TODEGREES(1) returns 57.29578 (1 * 180/π).

  • TODEGREES(Null) returns Null.

  • TODEGREES(2.5) returns 143.239449 (2.5 * 180/π).

ToNumber

ToNumber(x, [bIgnoreErrors], [keepNulls], [decimalSeparator]): Converts a string (x), to a number. ToNumber accepts strings that can be interpreted as scientific notation double precision. By default, the period is used as the decimal separator. Before (not after) the decimal separator (if any), all plausible thousands separators are removed: Space, comma, period, and apostrophe. You can write "123 456’789.012345".

ToNumber can return an integer value with 64 bits of precision if the input string looks like an integer and is in range.

Non-Numeric Values

ToNumber evaluates the string from the beginning and stops when it encounters a non-numeric value (ignoring commas, periods, spaces, and apostrophes).

For example, the string "June 2022" returns 0 or [Null] (depending on configuration), whereas "2022 June" returns 2022.

Optional Parameters

  • bIgnoreErrors

    • (default) 0 or false reports conversion error messages.

    • 1 or true ignores conversion errors.

  • keepNulls

    • (default) 0 or false converts non-numeric values (including null) to zero.

    • 1 or true converts non-numeric values to null.

  • decimalSeparator: The decimal separator of the incoming string.

    • (default) "." specifies a period as the decimal separator.

    • "," specifies a comma as the decimal separator.

    The decimalSeparator parameter ignores whatever is the designated thousands separator (space, period, comma, or apostrophe) of the incoming string.

Example

  • ToNumber("878") returns the string 878 as a number.

  • ToNumber("4.256411411E9") returns the number 4256411411.0.

  • ToNumber("9223372036854775807") returns 9223372036854775807. However, ToNumber("9.223372036854774273e18") returns a double, which will lose precision and be rounded up to 9223372036854775808.

  • ToNumber("Number", "false") returns 0 with conversion error: TONUMBER: Number lost information in conversion.

  • ToNumber("Number", 0, 0) returns 0 with conversion error: TONUMBER: Number lost information in conversion.

  • ToNumber("Number", 1, 0) returns 0 with no conversion error.

  • ToNumber("Number", 1, 1) returns [Null] and no conversion error.

  • ToNumber("123456,789", 1, 1, ",") returns 123456.789 as a number.

  • ToNumber("123.456,789", 1, 1, ",") returns 123456.789 as a number. This is because the period is interpreted as the thousands separator when it comes before the specified decimal separator via decimalSeparator.

  • ToNumber("June 2022") returns 0. The function immediately encounters a non-numeric value and converts it to 0.

  • ToNumber("2022 June") returns 2022 as a number. It stops when it encounters a non-numeric value, the J in June.

  • ToNumber("6/1/2022") returns 6 as a number. It stops when it encounters a non-numeric value, the /.

  • ToNumber("2022 5:00:00") returns 20225 as a number. It stops when it encounters a non-numeric value, the : after 5. Spaces before a decimal separator are ignored.

ToRadians

ToRadians(x): Converts a numeric degree value (x) to radians via the (x)° × π/180 calculation. Please note that x must be a numeric value and can’t include the degree symbol (°).

Example

  • TORADIANS(0) returns 0 (0 * π/180).

  • TORADIANS(1) returns 0.017453 (1 * π/180).

  • TORADIANS(5) returns 0.087266 (5 * π/180).

  • TORADIANS(2.5) returns 0.043633 (2.5 * π/180).

ToString

ToString(x, [numDec], [addThousandsSeparator], [decimalSeparator]): Converts a numeric parameter (x) to a string using numDec* decimal places. The default selection uses a period as the decimal separator. ToString treats Int64 values differently, so as to not lose precision in the conversion. ToString(value, 0, 1) with value=18014398509481983 precisely gives 18,014,398,509,481,983.

*The maximum value for the numDec parameter is 100.

Optional Parameters

  • addThousandsSeparator

    • (default) 0 formats the numeric string without a thousands separator.

    • 1 formats with a thousands separator. By default, the thousands separator is a comma unless "," is specified for decimalSeparator, in which case the thousands separator is a period.

    • "," specifies a comma as the thousands separator.

    • "." specifies a period as the thousands separator.

    • " " specifies a space as the thousands separator.

    • "'" specifies an apostrophe as the thousands separator.

  • decimalSeparator

    • (default) "." specifies the period as the decimal separator.

    • "," specifies the comma as the decimal separator.

Example

  • ToString(10, 0) returns 10 as a string.

  • ToString(10.4, 2) returns 10.40 as a string.

  • ToString(100.4, 2) returns 100.40 as a string.

  • ToString(1000.4, 2, 1) returns 1,000.40 as a string.

  • ToString(123456.789, 3, 1, ",") returns 123.456,789 as a string.

  • ToString(123456.789, 3, 0, ",") returns 123456,789 as a string.

  • ToString(1234567.89, 2, ".", ",") returns 1.234.567,89 as a string.

  • ToString(1234567.89, 2, " ", ",") returns 1 234 567,89 as a string.

  • ToString(1234567.89, 2, "'", ",") returns 1'234'567,89 as a string.

In this section: