11
In Excel, you can only change the separators of a CSV file with a little workaround. This article explains how to do it and what to watch out for.
Excel: How to change the separator of a CSV file using the regional settings
With a CSV file, you can export spreadsheets and databases that can be read by all common editors. This is useful for exchanging data sets, for example. However, one problem is that there is no uniform standard for separators. In Germany, for example, a semicolon is used as a separator between data. In the USA, a comma is used instead.
- This is problematic, for example, when setting up multiple user accounts in Microsoft 365 using a CSV file. Since Excel was developed in the United States, you have to use the comma as the separator.
- However, by default, changing to a comma-separated CSV file is not possible. Instead, you have to change the region settings on your PC.
- To do this, open the Windows Control Panel, click “Time and Region” in the overview, and then click “Change date, time, or number format”.
- Select “English (United States)” in the drop-down menu under Format.
- To save the settings, click on “Apply” and “OK”.
- You can then create the CSV file as usual. To do this, go to “File” and “Export”.
- Here, click on “Change file type” and select “CSV (separator-separated)”. After clicking on “Save as”, you can specify a file name and location.
Excel: reason for the different separators
The reason for the different separators depending on the region is the language.
- In the USA, a point is used as the decimal separator. This leaves the comma as a separator and it was therefore set as a separator.
- In Germany, on the other hand, the comma is used as the decimal separator. This means that it can no longer be used as a separator in a CSV file. Instead, a semicolon is used.
- Unfortunately, Microsoft Office does not yet allow you to change the separators directly in the export settings, so you have to resort to the trick of changing the regional settings.
