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.
Solved! Go to Solution.
Hi @dshaw ,
Replace ',' to nothing i.e '' and then use tonumber.
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.
@CharlieS You can remove a few characters from your expression to the same effect.
ToNumber(REGEX_Replace([Input],"[^-.\d]",""))
Thanks
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.
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.