27

Is there a way to tell Excel which delimiter to use when producing a CSV, e.g. tab or pipe?

Improve this question
1
  • 1
    There's a slightly better way, than changing Regional settings, which is a system wide change you probably don't want to do. If you want a semicolon, being the second most common delimiter, you can do this instead: - In Excel click File > Options > Advanced, then Advanced Options. - In the Editing options section, find Use system operators. - Uncheck it to override the system settings. - Set Decimal separator to comma (,) and Thousands separator to period (.). Clicking OK ensures that Excel uses semicolon (;) as the delimiter. At least it only changes the Excel settings.
    – Miklos Kallo
    Commented Sep 4, 2023 at 10:51

2 Answers 2

Reset to default
28

Assuming we're dealing with Windows as your OS...

  • Edit your Regional Settings or Windows (Regions and Language).
  • On the Formats tab hit Additional Settings....
  • Change the List Separator to the character you want to use as your CSV separator.

enter image description here

  • Apply those changes.
  • Go back to Excel and save the file as a CSV like you normally would and it should be saved using your chosen separator.

enter image description here

More info/image source

Improve this answer
6
  • 2
    For tab separated files, you can just use the built in file format Text (Tab delimited) (*.txt).
    – Richard
    Commented Jul 14, 2014 at 18:01
  • 3
    This is not a good solution if you have to export data frequently in different formats. Any recommendations on that without changing defaults?
    – Anyone
    Commented Jul 12, 2018 at 8:04
  • 6
    This is terrible. Then again - so it goes with many MS products.
    – WestCoastProjects
    Commented Nov 3, 2018 at 5:03
  • 4
    This is ridiculous from Microsoft's part. If I commit a .csv file to a repository, how can I know if every user will be able to open that file correctly (with correctly recognized delimiter?). If my file has a ";" delimiter and the user's region expects "," the file is going to break. It doesn't help that SEP=; will be removed by excel on save
    – user1055169
    Commented Apr 28, 2020 at 11:03
  • LibreOffice calc...when you go to Save-As type CSV, you have the option to pick export filters...for instance to customize the separator in the output. It is very common that a user will have one locale (e.g. Germans using ; separated CSV) but need to produce a CSV file for low-tech American SaaS that only accepts , separated CSV. Changing Excel's locale settings to produce this one file is not a reasonable option.
    – mattpr
    Commented Jan 25, 2021 at 11:10
4

I don't see an option immediately in Excel, but I can offer some advice.

Remember that CSV files are Comma Separated Values, which follows a standard: Data, a comma, data, a comma, etc. To break this means it will not follow the specification.

What happens if you have a comma in your data though? Excel will automatically surround that cell's content with quotation marks ("), so the program importing the file will know when that cell actually ends.

If you do not want a comma as a separator, you can save the file as a CSV still, then edit it manually. Open a text editor like Notepad or Notepad++. Do a search for whatever character you want to use (i.e. in Notepad, go to Edit->Find). If you want a pipe, search for a pipe (|). If you hit any results, do not use this character. To search for a tab easily, I'd recommend using Notepad++.

Once you find that the character you want to use isn't being used elsewhere in your document, then you can do a Search and Replace. In Notepad, go to Edit->Replace.... In 'Find what:', type a comma. In 'Replace with:', type the character you want to use for the separator. The next part is to hit Find Next. The reason for this, is that if there are commas in your data as well, this will break your file. Only replace the commas that are delimiters of the data, and nothing in quotation marks.

Once this is done, you can save the file. If you want to import it, I'd recommend renaming it to a .TXT file, so Excel will ask you what the Field Delimiter Character is. If you choose to open a .CSV file, it will automatically use a comma as the delimiter, which is what you don't want.

Improve this answer
3
  • 5
    There is no official spec for csv files. There are only conventions. CSV generally means comma separated, but data files using other delimiters are often referred to as CSV files.
    – dusktreader
    Commented Dec 20, 2016 at 18:06
  • 2
    You can also say that CSV stands for character separated values, it all depends on the decimal separator. Every language that does not use a dot as the decimal separator has to use something else than a comma to separate the values. That's why CSV files created with German language settings use a semicolon.
    – alpipego
    Commented Nov 8, 2017 at 7:41
  • Agreed, CSV is very common format and you can't limit the delimiter to comma. If you think about it, even Excel offers various and even user specified delimiters, when importing a CSV file (so it is, indeed, a serious shortcoming you can anly produce anything else, than your system setting by jumping through hoops). R for example offers various delimiters when opening and saving char delimited files.
    – Miklos Kallo
    Commented Sep 4, 2023 at 10:59

You must log in to answer this question.

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