Easiest way to open CSV with commas in Excel
Easiest way to open CSV with commas in Excel
Easiest way to open CSV with commas in Excel
Re: Easiest way to open CSV with commas in Excel
This is what worked for me - it is a combination of some of the answers here.
(Note: My PC is using a Czech format settings that format numbers like this: `1 000,00`, i.e., comma is used as a decimal separator and space as a thousands separator. The default list separator in system settings is `;`.)
- I changed the ***system* List separator** to a comma in Control Panel -> Region -> Additional settings
- **In Excel** 2013, I went to Options -> Advanced and **unchecked *Use system separators*** (under "Editing Options", which is the first section)
- In the same options dialog, I set **decimal separator in Excel to `.`** and the thousands separator to `,` (the thousands separator probably doesn't matter but I wanted to make it consistent with the US formatting)
An alternative to steps 2+3 would be to change these settings in the system settings (step 1) but I generally want to have numbers formatted according to a Czech locale.
**Downsides**: in Excel, I now need to enter decimal numbers using the US locale, i.e. using the dot instead of a comma. That feels a bit unnatural but is an acceptable trade-off for me. Fortunately, the comma key on my num-pad turned to the dot key automatically (and only in Excel - other apps still output a comma).
(Note: My PC is using a Czech format settings that format numbers like this: `1 000,00`, i.e., comma is used as a decimal separator and space as a thousands separator. The default list separator in system settings is `;`.)
- I changed the ***system* List separator** to a comma in Control Panel -> Region -> Additional settings
- **In Excel** 2013, I went to Options -> Advanced and **unchecked *Use system separators*** (under "Editing Options", which is the first section)
- In the same options dialog, I set **decimal separator in Excel to `.`** and the thousands separator to `,` (the thousands separator probably doesn't matter but I wanted to make it consistent with the US formatting)
An alternative to steps 2+3 would be to change these settings in the system settings (step 1) but I generally want to have numbers formatted according to a Czech locale.
**Downsides**: in Excel, I now need to enter decimal numbers using the US locale, i.e. using the dot instead of a comma. That feels a bit unnatural but is an acceptable trade-off for me. Fortunately, the comma key on my num-pad turned to the dot key automatically (and only in Excel - other apps still output a comma).