Substitutes specific character partly in cell

[Blesbie]

Hej,
I need your help please. I have data from a .csv file where unluckily the decimal and the thousand symbol are both ".". Therefore I would like to remove all "." except for the last one, which is the actual decimal-dot.

As a formula I use: =IF((LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>1),SUBSTITUTE(A1,".","",1),A1), but this only gives me the possibility to change the first ".". I would prefer to exchange all except for the last one. Also I would prefer to let it run as a macro, but there I'm stuck. I don't find a proper starting point. I think I need to count the amount of "." in each cell and if this amount is >1, I should replace them by "".

But I don't know how to express it. Can somebody please help me?

Thanks a lot!
Greetings
Moni

[jindon]

Try

Please Login or Register  to view this content.

[Blesbie]

Thanks for the reply. Unfortunately I don't understand the code and it also doesn't do anything... It loads a bit, but the numbers stay the same. Maybe if you explain a bit, I would be able to find the error myself.

[jindon]

Then I need to see your workbook.

[PCI]

It would be nice to have some sample of the data, to prepare the macro properly

[PCI]

See next code

Please Login or Register  to view this content.

[jolivanes]

Try this on a copy of your workbook.

Please Login or Register  to view this content.

[Blesbie]

Thanks so much at all of you. The codes of PCI and jolivanes work both :-) Since PCI does not even need an extra column, I will use that code, but thanks to all of you. You helped me really a lot :-)

[PCI]

Just for the fun a remake with a slight adjustment

Please Login or Register  to view this content.

[jolivanes]

Don't need an extra column.

Please Login or Register  to view this content.