Converting Strings to Numbers Error

[dshaw]

I need some help, thoughts, and solutions to a problem I am having converting a string format to a number.  In short, I am getting conversion errors and once the string is converted to number, it is being truncated--for example 1,517 string converts to 1.  I am using the Multi-formula tool with the conversion ToNumber formula.  I have tried using double, float, and int64 but the same results appear to keep happening.  The data is in a csv file (hence the string).  I have tried to cleanse the data but this result keeps happening.

I have also tried converting into a new column as well, but the conversion error or truncation keeps occurring. So any thoughts would be greatly appreciated.  

[MarqueeCrew · Accepted solution]

Replace([field],",","")

Get rid of those pesky commas! That should help.

Cheers,

Mark

[benakesh]

Hi @dshaw ,

Replace ','   to  nothing  i.e  ''  and then  use tonumber. 

[CharlieS]

I suggest using RegEx to remove any characters that will cause problems for the numeric conversion (like ','). This can be achieved with the following expression in a Formula tool where "input" is the field. 

ToNumber(REGEX_Replace([Input],"[^\-\.\d*]",""))

The REGEX_Replace function here will replace anything that is not a digit, negative sign, or period with nothing, effectively removing it before the field is converted to a numeric type. 

[PhilipMannering]

@CharlieS You can remove a few characters from your expression to the same effect. 

ToNumber(REGEX_Replace([Input],"[^-.\d]",""))

[dshaw]

Thanks

[Marko1986]

Hello there,

But what if i DON´T want to remove "those pesky commas"? They are crucial to my work that I need to do. I need those numbers to be exactly as they are.

[Marko1986]

Hello again,

Still having problems with annoying conversion error on Alteryx. I'm beginner at this but this should be 1% of job that I need to do and it constantly takes a lot of my time. Please help me with the situation

I want to convert String to Double but it constantly gives me error lost in translation.

I tried everything from Replace, ReplaceChar, RegEx(which deletes my negative number that I need!), To number doesn t help at all. 

All in all on every flow that I start to make, I lose to much time on this area.

Sending you screenshots in attachment and I would really need solution for this conversion. I need my numbers with commas to stay as they are. If they are negative, they need to be negative at the end.