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
decimalSeparatorparameter 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 viadecimalSeparator.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)
0formats the numeric string without a thousands separator.1formats with a thousands separator. By default, the thousands separator is a comma unless "," is specified fordecimalSeparator, 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.
UnicodeNormalize
UnicodeNormalize(String, Form): Converts text data in the provided string into a standardized Unicode form. Use the required Form parameter (case-insensitive) to define the normalization form. You must manually enter one of the below normalization form options (this parameter can't be populated via an upstream field.
Normalization helps ensure that different representations of the same text are treated equivalently. It reduces ambiguity and simplifies text processing tasks such as searching, sorting, and comparing. It's particularly important in environments where text can originate from various sources or be processed by different systems with differing encoding standards.
Parameters
String: The provided string that you need to normalize.Form: Options for how to normalize your string. These are the acceptable normalization forms (String format and quotes are required):NFC (Normalization Form Canonical Composition): NFC composes characters and sequences of characters in a text into a single precomposed form whenever possible. This form is commonly used for data interchange and storage.
NFD (Normalization Form Canonical Decomposition): NFD decomposes characters and sequences of characters into their base characters and combines characters. This form can be useful for tasks such as searching and comparing text.
NFKC (Normalization Form Compatibility Composition): NFKC first applies a compatibility decomposition, then composes characters and sequences of characters into a single precomposed form whenever possible. This form is often used for compatibility and normalization of legacy data.
NFKD (Normalization Form Compatibility Decomposition): NFKD applies a compatibility decomposition to characters and sequences of characters, breaking them down into their base characters and combining characters. This form can also be useful for compatibility and normalization purposes.
Example
UnicodeNormalize("i⁹","NFKD")returns i9.UnicodeNormalize("¼","NFKC")returns 1/4.UnicodeNormalize("㌄","NFC")returns ㌄.UnicodeNormalize("㌄","NFKC")returns イニング.