Expand my Community achievements bar.

SOLVED

number format in download csv's

Avatar

Level 1

In the csv downloads, numbers are formatted to use a comma as a thousand separator, and my Excel (on European settings, so with a decimal comma and a dot as a thousands separator) this gives trouble: a number like "12,340" will be read as "12,34" and changing the comma to a dot results in 1234 instead of 12340.

I suppose this would be easily solved by removing number formatting.

1 Accepted Solution

Avatar

Correct answer by
Level 1

Changing the number format does not work, my excel does not recognize a string with two commas in there as a number at all, so it does not allow to change the number format. With currency it is even worse: the csv will give me something like "11,494,320 EUR". There is no way any excel would ever translate this to a number.

The only workaround I have found is replacing the commas by dots (being my 1000-delimiter) with control-V and replacing " EUR" with nothing. But that is nog very elegant and goes wrong with figures ending on 0 (since "15,720" is read as "15,72".)

M!

View solution in original post

3 Replies

Avatar

Employee

Hi, 

Its a good suggestion and I would discuss internally if something could be done about this.

Meanwhile as a workaround, you could select the number cells in excel, right click and select Format cells, select category as 'number' and uncheck 'use 1000 separator'. 

TM

Avatar

Correct answer by
Level 1

Changing the number format does not work, my excel does not recognize a string with two commas in there as a number at all, so it does not allow to change the number format. With currency it is even worse: the csv will give me something like "11,494,320 EUR". There is no way any excel would ever translate this to a number.

The only workaround I have found is replacing the commas by dots (being my 1000-delimiter) with control-V and replacing " EUR" with nothing. But that is nog very elegant and goes wrong with figures ending on 0 (since "15,720" is read as "15,72".)

M!

Avatar

Level 1

I had the same problem importing a CSV in an european version of excel.
This is the solution found: when you select the function "text import", at the very last step of the import wizard, you'll find an "Advanced" menu -- from that menu, you can set the decimal and the thousand separator format.
Hope you'll find it useful.

I'll attach an Italian image of my menu.