How to change the format of numeric values?

[schee013]

Hello Community! I have a set of data where the numeric values are in this format:-

20.491.802,46 (Note that the thousands separator is "." instead of "," and the decimal point is "," instead of ".")

I would like to change it so that the thousands separator is "," and the decimal point is ".", hence the end result should look like 20,491,802.46. 

I tried using the replace function to first replace the "." to "," and then replace the "," to ".", but it doesn't seem to work because the thousands separators that was just replaced to "," would be replaced again to ".".

Appreciate any ideas on how I can go about this.

Thank you for your help! 😊

[mbarone · Accepted solution]

Kind of a sneaky way to do it, but I like the following:
Replace(Replace(Replace([Value],',','|'),'.',','),'|','.')

First replace the comma with a pipe.  Then replace the decimals with commas, then replace that pipe with the decimal 🙂

[schee013]

Genius!!!! Thank you!

[mbarone]

Sure thing!