How to change Excel CSV delimiter to comma or semicolon

The tutorial shows how to change CSV separator when importing or exporting data to/from Excel, so you can save your file in the comma-separated values or semicolon-separated values format.

Excel is diligent. Excel is smart. It thoroughly examines the system settings of the machine it's running on and does its best to anticipate the user's needs … quite often to disappointing results.

Imagine this: you want to export your Excel data to another application, so you go save it in the CSV format supported by many programs. Whatever CSV option you use, the result is a semicolon-delimited file instead of comma-separated you really wanted. The setting is default, and you have no idea how to change it. Don't give up! No matter how deep the setting is hidden, we'll show you a way to locate it and tweak for your needs.

What delimiter Excel uses for CSV files

To handle .csv files, Microsoft Excel uses the List separator defined in Windows Regional settings.

In North America and some other countries, the default list separator is a comma, so you get CSV comma delimited.

In European countries, a comma is reserved for the decimal symbol, and the list separator is generally set to semicolon. That is why the result is CSV semicolon delimited.

To get a CSV file with another field delimiter, apply one of the approaches described below.

Change separator when saving Excel file as CSV

When your save a workbook as a .csv file, Excel separates values with your default List separator. To force it to use a different delimiter, proceed with the following steps:

  1. Click File > Options > Advanced.
  2. Under Editing options, clear the Use system separators check box.
  3. Change the default Decimal separator. As this will change the way decimal numbers are displayed in your worksheets, choose a different Thousands separator to avoid confusion.

Depending on which separator you wish to use, configure the settings in one of the following ways.

To convert Excel file to CSV semicolon delimited, set the default decimal separator to a comma. This will get Excel to use a semicolon for the List separator (CSV delimiter):

  • Set Decimal separator to comma (,)
  • Set Thousands separator to period (.)
    Save Excel file as CSV semicolon delimited

To save Excel file as CSV comma delimited, set the decimal separator to a period (dot). This will make Excel use a comma for the List separator (CSV delimiter):

  • Set Decimal separator to period (.)
  • Set Thousands separator to comma (,)
    Save Excel file as CSV comma delimited

If you want to change a CSV separator only for a specific file, then tick the Use system settings check box again after exporting your Excel workbook to CSV.

Note. Obviously, the changes you've made in Excel Options are limited to Excel. Other applications will keep using the default List separator defined in your Windows Regional settings.

Change delimiter when importing CSV to Excel

There are a few different ways to import CSV file into Excel. The way of changing the delimiter depends on the importing method you opted for.

Indicate separator directly in CSV file

For Excel to be able to read a CSV file with a field separator used in a given CSV file, you can specify the separator directly in that file. For this, open your file in any text editor, say Notepad, and type the below string before any other data:

  • To separate values with comma: sep=,
  • To separate values with semicolon: sep=;
  • To separate values with a pipe: sep=|

In a similar fashion, you can use any other character for the delimiter - just type the character after the equality sign.

Once the delimiter is defined, you can open your text file in Excel like you normally would, from Excel itself or from Windows Explorer.

For example, to correctly open a semicolon delimited CSV in Excel, we explicitly indicate that the field separator is a semicolon:
Indicate separator directly in a CSV file.

Choose delimiter in Text Import Wizard

Another way to handle a csv file with a delimiter different from the default one is to import the file rather than open. In Excel 2013 an earlier, that was quite easy to do with the Text Import Wizard residing on the Data tab, in the Get External Data group. Beginning with Excel 2016, the wizard is removed from the ribbon as a legacy feature. However, you can still make use of it:

  • Enable From Text (Legacy) feature.
  • Change the file extension from .csv to .txt, and then open the txt file from Excel. This will launch the Import Text Wizard automatically.

In step 2 of the wizard, you are suggested to choose from the predefined delimiters (tab, comma, semicolon, or space) or specify your custom one:
Choosing a delimiter for the csv file

Specify delimiter when creating a Power Query connection

Microsoft Excel 2016 and higher provides one more easy way to import a csv file - by connecting to it with the help of Power Query. When creating a Power Query connection, you can choose the delimiter in the Preview dialog window:
Choose the CSV delimiter when creating a Power Query connection.

Change default CSV separator globally

To change the default List separator not only for Excel but for all programs installed on your computer, here's what you need to do:

  1. On Windows, go to Control Panel > Region settings. For this, just type Region in the Windows search box, and then click Region settings.
    Open Windows Region settings.

  2. In the Region panel, under Related settings, click Additional date, time, and regional settings.
    Additional date, time, and regional settings

  3. Under Region, click Change date, time, or number formats.
    Change date, time, or number formats

  4. In the Region dialog box, on the Formats tab, click Additional settings
    Additional settings

  5. In the Customize Format dialog box, on the Numbers tab, type the character you want to use as the default CSV delimiter in the List separator box.
    Type the character you want to use as the default CSV delimiter.

    For this change to work, the List separator should not be the same as Decimal symbol.

  6. Click OK twice to close both dialog boxes.

When done, restart Excel, so it can pick up your changes.

Notes:

  • Modifying the system settings will cause a global change on your computer that will affect all applications and all output of the system. Do not do this unless you are 100% confident in the results.
  • If changing the separator has adversely affected the behavior of some application or caused other troubles on your machine, undo the changes. For this, click the Reset button in the Customize Format dialog box (step 5 above). This will remove all the customizations you've made and restore the system default settings.

Changing List separator: background and consequences

Before changing the List separator on your machine, I encourage you to carefully read this section, so you fully understand possible outcomes.

First off, it should be noted that depending on the country Windows uses different default separators. It's because large numbers and decimals are written in different ways across the globe.

In the USA, UK and some other English-speaking countries including Australia and New Zealand, the following separators are used:

Decimal symbol: dot (.)

Digit grouping symbol: comma (,)

List separator: comma (,)
The USA and UK regional settings

In most European countries, the default list separator is a semicolon (;) because a comma is utilized as the decimal point:

Decimal symbol: comma (,)

Digit grouping symbol: dot (.)

List separator: semicolon (;)
European regional settings

For example, here's how two thousand dollars and fifty cents is written in different countries:

US and UK: $2,000.50

EU: $2.000,50

How does all this relate to the CSV delimiter? The point is that the List separator (CSV delimiter) and Decimal symbol should be two different characters. That means setting the List separator to comma will require changing the default Decimal symbol (if it's set to comma). As the result, numbers will be displayed in a different way in all your applications.

Moreover, List separator is used for separating arguments in Excel formulas. Once you change it, say from comma to semicolon, the separators in all your formulas will also change to semicolons.

If you are not ready for such large-scale modifications, then change a separator only for a specific CSV file as described in the first part of this tutorial.

That's how you can open or save CSV files with different delimiters in Excel. Thank you for reading and see you next week!

You may also be interested in

8 comments to "How to change Excel CSV delimiter to comma or semicolon"

  1. hayder says:

    [ 0. 0. 0. 1. 7. 6. 0. 0. 0. 0. 0. 2. 4. 1.
    0. 0. 2. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0.
    0. 0. 0. 0. 0. 0. 0. 34. 128. 106. 6. 0. 23. 4.
    1. 95. 128. 42. 18. 7. 128. 15. 2. 18. 16. 4. 10. 23.
    46. 6. 10. 73. 79. 1. 0. 1. 19. 12. 29. 128. 83. 27.
    10. 5. 29. 7. 3. 23. 82. 128. 128. 25. 128. 71. 13. 15.
    25. 40. 66. 51. 37. 43. 25. 81. 128. 19. 5. 12. 47. 43.
    20. 73. 13. 19. 14. 8. 40. 77. 52. 23. 48. 22. 14. 6.
    5. 38. 31. 37. 78. 29. 4. 1. 37. 36. 56. 81. 20. 9.
    3. 6.];[ 0. 0. 0. 1. 7. 6. 0. 0. 0. 0. 0. 2. 4. 1.
    0. 0. 2. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0.
    0. 0. 0. 0. 0. 0. 0. 34. 128. 106. 6. 0. 23. 4.
    1. 95. 128. 42. 18. 7. 128. 15. 2. 18. 16. 4. 10. 23.
    46. 6. 10. 73. 79. 1. 0. 1. 19. 12. 29. 128. 83. 27.
    10. 5. 29. 7. 3. 23. 82. 128. 128. 25. 128. 71. 13. 15.
    25. 40. 66. 51. 37. 43. 25. 81. 128. 19. 5. 12. 47. 43.
    20. 73. 13. 19. 14. 8. 40. 77. 52. 23. 48. 22. 14. 6.
    5. 38. 31. 37. 78. 29. 4. 1. 37. 36. 56. 81. 20. 9.
    3. 6.];True

    Please need to make a code or tools to be three above details as a column, its mean [ ] [ ] True as a three columns

  2. sedlo says:

    This instruction doesn't work:
    "To save Excel file as CSV comma delimited, set the decimal separator to a period (dot). This will make Excel use a comma for the List separator (CSV delimiter):"

    • Angélico Manhique says:

      I had the same problem, could not change the list separator modifying it in Excel 2016 Professional. The file I was trying to convert, had to be used in another program that uses the comma as list separator (forScore).
      I confirm the second option, the global change of the list separator. This has the draw-back that it interferes with other simulating programs. So, after converting the file, I had to restore the configuration for my other simulators to work properly again.

      How did you test the first approach?

      • Hi Angélico,

        Here's what I did:

        - Performed the steps described in "Change separator when saving Excel file as CSV". In particular, set Decimal separator to period (.) and Thousands separator to comma (,).
        - Saved the workbook as CSV file.
        - Opened the CSV file in Notepad to check which separator is actually used. In my case, the result is always comma-separated values.

        • Stefan says:

          Hi Svetlana,
          for me with Excel 365 it is not working.
          Allthough doing all steps and chekcing it twice, result is still semicolon separataed.

          • Tira says:

            Hi Stefan,

            The semicolon is still there since perhaps you have missed this part "For this change to work, the List separator should not be the same as Decimal symbol." on the article.

            Good luck.

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)