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