21

I have Excel 2011 on my Mac, and when I try to export a .xls file into .csv I end up having a semicolon separated file instead of .csv.

p.s. The System preferences for my keyboard input is Italian

What is the matter?

24

Excel for Mac currently has no setting to change the CSV separator from the app itself.

The chosen value separator depends on your Region and your region's default number separators.  To change them, go to “System Preferences” > “Language & Region”:

System Preferences > Language & Region

Then you need to verify the number separators in “Advanced …” > “General”.

Advanced > General

Here are the rules Excel uses to determine how it saves CSV files:

  • If your language / region / locale uses comma (,) for decimal separation (i.e., π (pi) is displayed as 3,1416), then Excel will save using semi-colons (;)
  • If it uses a dot (., a.k.a. full stop or period), then it will delimit with commas (,).

Note:

  • You need to change your general region for it to work. For example, if your region is Germany (which uses , for decimal separation), Excel will always use ; for CSVs, even when changing the decimal point under “Advanced” settings. You could, for example, if you want , as a CSV separator, choose the United States as a region.
  • You need to relaunch Excel for it to notice. Quit the app, change the region, then start it again.
| improve this answer | |
  • 1
    Thank you but it doesn't work. I've tried both of your solutions.... – Cris Cole Oct 19 '15 at 14:42
  • Works repeatably here, switch both number & currency comma separators to 'dot' separators & vice versa. Relaunch Excel, open xlsx file, save as csv or windows csv, it saves with the alternate comma/semi-colon – Tetsujin Oct 19 '15 at 14:57
  • I tried again, it doesn't work.... both i59.tinypic.com/2z4lt3m.png AND i57.tinypic.com/13yfbci.jpg – Cris Cole Oct 19 '15 at 15:34
  • 4
    changing the decimal separator didnt work for me. I add to change the whole "region" from "France" to "United States". – cyrilchampier Mar 9 '16 at 15:51
  • 1
    I tried to clarify your post – I noticed that the actual decimal point settings don't matter to Excel. It's the region that needs to change. So for me, living in Germany, I had to change it to US to correctly interpret CSV with commas. I consider this a bug or at least a lack of features in Excel, which should have an option to override the system's settings. – slhck Aug 1 '17 at 9:50
6

I've tried the accepted answer with no success. (High Sierra + Excel 16.10) My only option has been to use open office, far away from Excel in this feature.

| improve this answer | |
  • +1 Installed OpenOffice and got what I need. Thanks! – geff_chang Mar 24 '18 at 13:19
  • 1
    Another option is to add "sep=," as the first line of the csv file. – v.shashenko Dec 18 '18 at 16:55
  • +1 for sep= :DD – user1721019 Oct 14 '19 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.