converting euro style numbers to us style

[erqazx]

I'm trying to convert 1.000.222,33 numbering to 1,000,222.33

So far I have just used find+replace, by removing '.' first and then just converting ',' to '.' However when I come across thousands ending with a zero (eg 1.060) this system fails and will give me 160.000 (one hundred sixty) instead if 1,060 (thousand sixty). So I have to adjust all these manually in fairly large sheets. Does anyone know how to fix this? Or does someone perhaps have a macro for this (I can't write VBA myself)? Any help much appreciated. Thanks!

 

[anglais428]

I think find and replace would still work. First replace "." with "!", the replace "," with "." then finally replace "!" with ","

 

[erqazx]

That doesn't work because then the last zero disappears. Anyone else?

 

[Gerald Higgins]

That doesn't work because then the last zero disappears.

Can you give us an example of what you mean by this ?

 

[P_Guru]

That doesn't work because then the last zero disappears. Anyone else?

It works for me.

 

[anglais428]

It should work, can you post a figure you would like me to test with? I did test with an ending zero and it kept it

 

[erqazx]

Can you give us an example of what you mean by this ?

if I replace the dot in 1.060 by '!', I get 1!06 instead of 1!060 (what I want)

So the last zero automatically disappears.. is this a setting I need to change? The last zero also disappears when I regularly type in a number

 

[anglais428]

change your numbers using the following formula (assuming your number is in cell A1)

=text(A1,"0.000")

Then perform the replace

 

[P_Guru]

if I replace the dot in 1.060 by '!', I get 1!06 instead of 1!060 (what I want)

So the last zero automatically disappears.. is this a setting I need to change? The last zero also disappears when I regularly type in a number

What's happening is that 1.060 (one thousand and sixty) is being converted to 1.060 (one point zero six) before the replacement takes place.

Maybe try replacing , with ! first.