5
2
When creating a CSV file from an XLSX workbook with the new Excel for Windows 8, I get a file filled with semicolons instead of colons.
How can I change that? I even had to reopen the file with Wordpad to find out why my file could not be exported to a data management program.
Paula
Posted 2013-11-07T20:30:29.770
Reputation: 59
8
Your Windows regional settings are set to use semi-colons instead of commas as the list seperator symbol. You'll have to re-configure those in order to force Excel to use colons instead of semi-colons.
Check out this page to find out how to do that: http://www.rawcomputing.co.uk/windows8/windows-8-tip12.html The list seperator symbol should be configurable on the window that pops up when clicking the "Additional settings..." button on the "Region" window.
Hauke P.
Posted 2013-11-07T20:30:29.770
Reputation: 484
1This change will also affect Opening of CSV files in Excel and possibly more windows programs.
If you want to make more compatible CSV file, put sep=, on top of the CSV file, then once you save the file, you can change List Separator back to whatever it was. – papo – 2015-12-30T03:11:28.607
Cool. I did not know that trick. You might want to add it as an own answer. – Hauke P. – 2016-01-01T10:18:17.120
Why does it use CSV file extension? CSV stands for COMMA separated list. If they use SEMICOLON separated list, they should use SSV, isn't they? – Dims – 2016-02-17T16:41:22.750
@Dims: Though this could be a question on its own, and this is not really the right place for an answer, I'll add my assumption here nevertheless: In some regions (like Germany) the decimal separator is a comma instead of a dot. That's why formulas will have to be written =SUM(0,8; 1,2) there instead of =JUST(0.8, 1.2). As Excel will export decimal values using the Windows regional settings, it coherently will also use its list separator. (Obviously the two separators should be different from each other - or else the line 1,2,3,4 in a CSV file could mean both 1.2, 3.4 and 1,2,3,4.) – Hauke P. – 2016-02-18T22:58:34.380
Note, that in newer Excel versions you can change the list separator right from within your Excel options – nixda – 2013-11-07T22:05:27.207
1Oh? I'm using Excel 2013 and just scrolled through the options without finding any option. Where exactly can you configure the list separator symbol in my version? – Hauke P. – 2013-11-08T09:30:48.397
Sorry, I may have confused thousands separator with list separator
– nixda – 2013-11-08T10:24:53.853Okay, yes. I found that option as well. What I didn't find was an option for list seperators though. – Hauke P. – 2013-11-08T15:43:18.960
2What's the new excel? 2013? – Raystafarian – 2013-11-08T00:06:20.217