Standardize decimal separator

[iferror]

Hi Excellers,
in a macro my goal is to read numbers from different xls and xlsx files, do some calculations and then export results in a csv.
The decimal separator in the output file is point. As i get input files from various region in the world, the decimal separator can vary.
I tried text to column as i have an entire column to standardize, but i may have done something wrong as it did not work for all the cases.

Input value 77.410,83 --> Output expected 77410.83
Input value 77410,83 --> Output expected 77410.83
Input value 77410.83 --> Output expected 77410.83
Input value 77,410.83 --> Output expected 77410.83

I am pretty sure someone here has already a smart solution to this problem.

 

[vletm]

iferror​

Is there always two decimals with input value as in Your sample?
... if Yes, then one possible solution would be:
# clear all possible decimal separators
# add needed decimal separator in correct place.

 

[Marc L]

Hi !​

I tried text to column as i have an entire column to standardize, but i may have done something wrong as it did not work for all the cases.

As it always works as expected just well setting the decimal separator via its last step Advanced button …​

​

 

[iferror]

Hi !​

As it always works as expected just well setting the decimal separator via its last step Advanced button …​

​

Totally agree....i am pretty sure i'm missing something. But how do i set DecimalSeparator if i have one file having , as decimal separator and no thousand separator, another with , as decimal separator and . as thousand separator, another with . as decimal separator and another with . as decimal and , for thousands?
Moreover, some values have no decimal and some doesn't. (this is to reply to vletm)


This is a few rows from a file

MyHeader

77410,83

-747,16

12504

11394,63

This is another file

MyHeader

12454

2075.83

3012.93

-2289.18

And finaly

MyHeader​

7.254,31​

1.673,64​

135​

1.323,56​

2.135,02​

 

[vletm]

iferror​

Your do some calculations and then export results in a csv.
... means that You have/use Your 'correct' decimal separator in Your Excel-file
... and while exporting .. You should able to know ... what decimal separator You should use for that csv-file
or
what do You mean?