Convert to number

StefanvanWyk

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

Mock data set.xlsx

Ladarthure

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!