Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Number format changes when I switch from "V_String" to "Int64"

5 - Atom



I'm currently trying to change the format of a column to be read as a number rather than a string. However when i use some of the numeric type formats my numbers will change from "1,862,541" to just "1". Does anyone know what would be the best data type to keep the same number format? I have already tried "Int64,32,16" and "double" and none of them seem to work. 


I just started learning Alteryx as part of a class, any feedback will be greatly appreciated. Thank you!







9 - Comet

dear @JuanGaspar 


I usually use the "data cleansing" tool to remove commas from numbers


Solution 211014 00.png


Solution 211014 01.png

8 - Asteroid

Like @Ar13f said, you can use the "Data Cleansing" tool. Put it before your "Select" tool to remove the commas in your VALUE column.


That solution should be enough since you seem to be dealing with integers/whole numbers. If you have to convert numbers with decimals, you can use the "Formula" tool instead. There's a function called REGEX_Replace that would allow you to remove just the commas and leave the decimal points in.

12 - Quasar
12 - Quasar

hi @JuanGaspar 

the function "ToNumber" does this very elegantly and works for numbers with decimal places too.


ToNumber(x, [bIgnoreErrors], [keepNulls], [decimalSeparator]): Converts a string (x), to a number.


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.


If your work often involves data conversion, check out this helpful resource: