CSV files in Excel: How to convert a comma-delimited file to a semicolon-delimited file, and vice versa

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 by 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:

generate csv step 1

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

generate csv step 2

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

generate csv step 3

Note that, not only are semicolons delimiters in this file, commas are decimal symbols.

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

regional settings

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

It is then a good practice to avoid using such symbols in CSV files when possible. For example, as shown, we can use a hyphen instead of a period . for formatting dates.


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

unreadable csv

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

unreadable csv opened in excel


One of the things we could do in order to display it properly is to change our region and/or number formats in the Windows Control Panel.

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


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

text to columns

In our first step, we should select the Delimited option:

text to columns step 1

We should only select the Comma as a Delimiter option:

text to columns step 2

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:

text to columns step 3

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

save as csv new


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 ,

edit csv in notepad

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

edit csv in notepad save&open

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

One thought on “CSV files in Excel: How to convert a comma-delimited file to a semicolon-delimited file, and vice versa”

Leave a Reply