Converting numbers that have commas and decimals

Steve07805

Hi,

I have some data that uses decimals and commas. For example

191,08

5.138,55

8.666,01

19,33

17.154,10

The data uses a decimal to split the thousands. And a comma to signify the decimal place.

I found a formula to convert the comma to decimals but end up with:

17.154.10 which does not work.

Do you know a way to convert this data so it would be as follows:

191.08

5138.55

8666.01

19.33

17154.10

Thank you!

Transformation

Accepted answers

BenMoss

I should add though, if you want the number to be treated like a number we don't even want the commas. So what I would actually do is something like

replace(

replace([field],".","")

,",",".")

In that we first replace full stops with nothing (i.e. removing what should be commas completely) and then replacing commas with a full stop.

Ben

All comments

BenMoss

I would use a formula like...

replace(

replace([field],",","A")

,".",","),"A",".")

This should work...

So first of all we place commas with the letter A, we then replace full stops with a comma, and the finally the letter A with full stops.

BenMoss

I should add though, if you want the number to be treated like a number we don't even want the commas. So what I would actually do is something like

replace(

replace([field],".","")

,",",".")

In that we first replace full stops with nothing (i.e. removing what should be commas completely) and then replacing commas with a full stop.

Ben

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703