Excel save as semicolon delimited

A CSV file is a plain text file used to store and transfer data between different, often incompatible, applications and systems.

In these plain text CSV files, each line of text is a data record. Also, each line of text (i.e. each data record) consists of one or more fields, separated by delimiters.

Those delimiters are originally commas [,], hence the name comma-separated values (CSV). However, data can in principle be delimited with a variety of special characters. Since commas are used as decimal symbols in many countries, data in CSV files in those countries will instead usually be delimited with semicolons [;].

Excel can read, edit and create CSV files. Lines of text are being treated as rows, and delimited fields as new cells.

Let’s look at the data in this example:

We can save that data in CSV file format by choosing Save As option:

This is how that data looks when we open our CSV file with a plain text editor such as Notepad:

Note that not only that semicolons are delimiters in this file, comma is also the decimal symbol.

All of this is defined by the Windows regional number format settings:

Not only would user which uses comma as a list separator have trouble reading this data because of incompatible List separator, he would most likely also have issues with our Decimal symbol and Digit grouping symbol.

It is than a good practice to avoid using such symbols in CSV files when possible. We can for example, as shown, use hyphen [-] instead of period [.] for formatting dates.

On the other hand, CSV file generated by user based in, for example, United States, would contain this data:

If we open that file in Excel, the data is not displaying properly:

First thing we can do in order to display it properly is to change our region and/or number formats in Windows Control Panel.

However, this is potentially problematic solution as we are breaking CSV files from our region by doing this.

Our next option is to fix our broken CSV file in Excel with the help of Convert Text to Columns wizard:

In our first step we should select Delimited option:

We should only select Comma as Delimiter option:

We also have to select every problematic column and instruct Excel what kind of data to expect. Specifically, in this example, if columns are expected to contain decimal numbers, we have to tell Excel to expect periods [.] as decimal separators so that Excel could convert those to our decimal symbols:

Once the wizard completes, if needed, we can save our modified CSV file:

Alternatively, we can manually edit our CSV file by using Notepad’s Replace All function.

In order to convert a comma-delimited file to a semicolon-delimited file, we have to:

1. Replace all commas [,] with semicolons [;]
2. Replace all periods [.] with commas [,]

Once we do that, we can open the file in Excel:

Vice versa, if we were to convert a semicolon-delimited file to a comma-delimited file, we would have to:

1. Replace all commas [,] with periods [.]
2. Replace all semicolons [;] with commas [,]

Dig deeper:

Text in Excel