28

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?

Improve this question

3 Answers 3

Reset to default
43

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
9
  • 1
    Thank you but it doesn't work. I've tried both of your solutions....
    – Cris
    Commented Oct 19, 2015 at 14:42
  • 1
    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
    Commented Oct 19, 2015 at 14:57
  • I tried again, it doesn't work.... both i59.tinypic.com/2z4lt3m.png AND i57.tinypic.com/13yfbci.jpg
    – Cris
    Commented Oct 19, 2015 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
    Commented Mar 9, 2016 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
    Commented Aug 1, 2017 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
6
  • +1 Installed OpenOffice and got what I need. Thanks!
    – geff_chang
    Commented Mar 24, 2018 at 13:19
  • 3
    Another option is to add "sep=," as the first line of the csv file.
    – v.shashenko
    Commented Dec 18, 2018 at 16:55
  • 1
    +1 for sep= :DD
    – user1721019
    Commented Oct 14, 2019 at 8:37
  • 1
    +1 this should be THE answer. Gold for sep=,. I needed tabs: sep=\t
    – Tom Siwik
    Commented Sep 1, 2020 at 15:20
  • How does one 'add something to the first line of the csv file'? Like, what does it mean and how do I do that if I'm in Excel?
    – Jem
    Commented Dec 2, 2020 at 21:38
-1

One more solution.

  1. Select the column.
  2. Click on Data, then click on Text to columns.
  3. Follow the wizard as follows, Delimited -> Select comma, Tab as delimiters -> Finish.
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .