Expand my Community achievements bar.

Check out the November edition of the Analytics Community Lens newsletter to see what's been trending in the last two months!
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

Level 10

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.