Introduction
If you have a CSV file with numbers formatted with a thousand separator as follows:
"123,456.00"
...then when you open the CSV in Excel it will automatically understand it's a number (not text despite the " ") and format it correctly. Excel is so clever!
Or is it? With English set as your Windows language yes. If you have your language setting on anything but English (French for example) then Excel is not so clever! Excel will import the values inside quotes " " as text. This is very annoying because you can then not use those numbers in pivot tables or even sum a column.
We need these numbers in a number format, not text. The comma for the thousand separator is causing the problem.
Solution 1
You could select the columns that contain the thousand separators and do a replace to remove them. This is fine if it's a one-off situation but if you have to work with CSV files formatted this way again and again, I would recommend one of the following solutions (especially Solution 3).
Solution 2
Change your language setting to English - it's not the best solution of course, but it works.
Click Start | Control Panel | Region and Language
Solution 3
Click Start | Control Panel | Region and Language (or search for "Region and Language")
If you have a CSV file with numbers formatted with a thousand separator as follows:
"123,456.00"
...then when you open the CSV in Excel it will automatically understand it's a number (not text despite the " ") and format it correctly. Excel is so clever!
Or is it? With English set as your Windows language yes. If you have your language setting on anything but English (French for example) then Excel is not so clever! Excel will import the values inside quotes " " as text. This is very annoying because you can then not use those numbers in pivot tables or even sum a column.
We need these numbers in a number format, not text. The comma for the thousand separator is causing the problem.
Solution 1
You could select the columns that contain the thousand separators and do a replace to remove them. This is fine if it's a one-off situation but if you have to work with CSV files formatted this way again and again, I would recommend one of the following solutions (especially Solution 3).
Solution 2
Change your language setting to English - it's not the best solution of course, but it works.
Click Start | Control Panel | Region and Language
Solution 3
Click Start | Control Panel | Region and Language (or search for "Region and Language")
Click Additional settings
Change the Digit grouping symbol to a comma (as shown
above)
If your data is comma delimited then change the List separator to a comma (as shown above). A more in-depth explanation of this can be found in another article I wrote recently 'CSV files do not open correctly'.
Click OK (you should exit this window and return in order to change Digit grouping)
Click Additional settings
Click Digit grouping and change it to 123,456,789
(as shown above)
Click OK, OK
Now try opening the CSV file in Excel again, the numbers should be formatted correctly, as numbers (you'll see them right aligned in their cells).
IMPORTANT: If there is still a problem, go into Excel, click File | Options | Advanced. Look for Use system separator and make sure it is enabled (ticked). But also, ensure the dot and comma are set correctly as the separators.
Solution 4
It is possible to tell Excel not to use the system language settings, here's how:
In Excel click File | Options | Advanced
As shown in the above screen shot, ensure that Use system separator is disabled (not ticked).
Make sure the Decimal separator is a dot and the Thousands separator is a comma.
Conclusion
Solution 4 is probably the simplest solution. But you might also find that Solution 3 is best as it will ensure there are no problems throughout all applications.
IMPORTANT: The above solutions are what I've discovered. Maybe there are other solutions, if there are then please feel free to write in the comments.
Excel Version?
Excel 2013 and 2010, probably earlier versions too - feel free to write in the comments below about your experiences.
Now try opening the CSV file in Excel again, the numbers should be formatted correctly, as numbers (you'll see them right aligned in their cells).
IMPORTANT: If there is still a problem, go into Excel, click File | Options | Advanced. Look for Use system separator and make sure it is enabled (ticked). But also, ensure the dot and comma are set correctly as the separators.
Solution 4
It is possible to tell Excel not to use the system language settings, here's how:
In Excel click File | Options | Advanced
As shown in the above screen shot, ensure that Use system separator is disabled (not ticked).
Make sure the Decimal separator is a dot and the Thousands separator is a comma.
Conclusion
Solution 4 is probably the simplest solution. But you might also find that Solution 3 is best as it will ensure there are no problems throughout all applications.
IMPORTANT: The above solutions are what I've discovered. Maybe there are other solutions, if there are then please feel free to write in the comments.
Excel Version?
Excel 2013 and 2010, probably earlier versions too - feel free to write in the comments below about your experiences.
Really nice tricks ! Thanks :)
ReplyDelete