community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Converting Strings to Numbers Error

Asteroid

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.  

Alteryx Certified Partner
Alteryx Certified Partner
Replace([field],",","")

Get rid of those pesky commas! That should help.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Quasar

Hi @dshaw ,

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

Alteryx Certified Partner
Alteryx Certified Partner

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. 

Alteryx Certified Partner

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

 

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

 

Asteroid

Thanks

Labels