Alteryx Designer Discussions

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

Convert to number

StefanvanWyk
7 - Meteor

Hi,

 

I have a problem.

 

I am trying to convert to a number format. However, the formatting used by the data source is not consistent.

 

Within the amount column there are numbers formatted 1760,00 (which is 1 760), then there is 120,000.00 (Which is 120 000) and then there is 63008,60 (Which should be 63 008.60).

 

When I try and clean-up with a formula to change the "," it changes the instances where the comma was used to spit the decimals. Any ideas? 

 

I have included a mock dataset

1 REPLY 1
Ladarthure
13 - Pulsar
13 - Pulsar

Hi @StefanvanWyk,

 

a simple solution if you always have decimals would be to replace both "." and "," by nothing so that you get the plain number for instance

 

1760,00 would become 176000

120,000.00 would become 12000000

63008,60 would become 6300860

 

transform these in number format with tonumber function and finally divide by 100 to get the decimal back!

 

Formula would be tonumber(ReplaceChar([Amount], ',.', ''))/100

 

Hope it helps!

Labels