Conversion Functions

Version:

2022.3

Last modified: November 29, 2022

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 53 bits of precision and can be up to 64 bits of length. It means that you can have, for example, a string that is 53 1-bits with 10 0-bits.

Example

BinToInt("101010101") results in 341.

If the string is exactly 32 or 64 characters long, it will be treated as a signed number:
BinToInt("11111111111111111111111111111011") results in -5.

The leading signs ‘+’ and ‘-‘ take precedence over treating 32 and 64 characters as a signed number:
BinToInt("-111") results in -7,

BinToInt("+111") results in 7.

All of following examples give the same value 9,223,372,036,854,774,784, or 0x7FFFFFFFFFFFFC00:
BinToInt("111111111111111111111111111111111111111111111111111101000000001") rounds up,

BinToInt("111111111111111111111111111111111111111111111111111110000000000") exact,

BinToInt("111111111111111111111111111111111111111111111111111110111111111") rounds down.

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, visit 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 53 bits).

Example

HexToNumber(dd) converts to the number 221.

IntToBin

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

IntToHex

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

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.

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.

Examples

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

ToNumber("4.256411411E9") returns the string 4256411411 as a number.

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 automatically interpreted as the thousands separator, while the comma is specified as the 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.

ToString

ToString(x, numDec, [addThousandsSeparator], [decimalSeparator]): Converts a numeric parameter (x) to a string using numDec decimal places. Default selection uses a period as the decimal separator.

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.

Examples

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.