How can we change an Excel CSV delimiter to a comma or semicolon in Microsoft Excel?

In this modern era of growing technology, it is well known that Microsoft Excel is diligent in action and intelligent (in performing calculations). And due to this reason it primarily examines the system settings of the given machine as it was running on and it does its best to grant the needs of the respective users' effectively, and some of the times, it disappoints too.

Let us now imagine that if an individual wants to export our Microsoft Excel data to the other application and for that purpose to be get achieved, we will be moving on saving it into the CSV format that are supported by many of the programs.

Moreover, whatever CSV option we make use of it, the result will be the semicolon-delimited file instead of getting the comma-separated file which we want to obtain.

In this tutorial, we will be discovering as well as discussing the various concepts related to changing of an Excel CSV delimiter file to a comma or semicolon in Microsoft Excel:

1. What Kind of separator does Microsoft Excel make use of for the CSV?
2. Saving the Microsoft Excel file as CSV with the various delimiters.
3. Opening or importing the CSV with the different available delimiters.
4. Changing the default CSV separator globally.
5. Changing of the list separator: background and its sequences.

What Kind of delimiter does Microsoft Excel make use of for CSV files?

To handle the .csv files Microsoft Excel uses the List separator that are usually defined in the Windows Regional settings. And it was well known that in North America and in some other countries, the default list separator is none other than the comma to get the CSV delimited.

Furthermore, in European countries a comma is primarily reserved for the decimal symbol and the list separators are set to semicolons. Due to this, the result that would be obtained would be the CSV semicolon delimited.

Now in order to obtain out the CSV files with the other delimiter field, we need to apply one of the below-given approaches as well.

Changing of the separator while saving the Excel file as CSV

When we save a workbook as a .csv file Microsoft Excel separates values with our default that is the List separator. And when we force it to make use of the different delimiter we will be making use of the below following steps which are as follows:

Step 1) First of all, we will click on the File menu and click on the options and under the option we will be clicking on the "advanced options" respectively.

Step 2) Now under the Editing options we will be clearing the Use system separators check box in this step.

Step 3) After that, we will change the default Decimal separator as changing this will change how the decimal numbers are displayed in our worksheets, and we will choose out the different Thousands of separators to avoid confusion effectively.

Depending on which respective separator we wish to make use of it, we will configure the settings in one of the following ways which are as follows:

To convert the Microsoft Excel files to the CSV semicolon delimited, for that, we will set the default decimal separator to a comma, and this will get Excel to make use of the semicolon for the List separator (CSV delimiter):

1. The setting of the Decimal separator to comma (,).
2. Set Thousands separator to the period (.)

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

• Set the Decimal separator to the period (.)
• Set Thousands separator to comma (,).

And if in case we want to change a CSV separator just for the sake of a specific file, we can make a tick on the Use system settings check box again just after exporting our Microsoft Excel workbook to CSV respectively.

Note: The particular changes which we have made in Microsoft Excel Options are limited to Excel. And alternatively, the other applications will use the default List separator, which was defined in our Windows Regional settings effectively.

Changing the delimiter when importing CSV to Microsoft Excel

It is well known that there various methods which can be used to import CSV files into Excel. And the way of changing the delimiter will primarily depend upon the importing process, which we have opted for efficiently.

Indication of the separator directly in the CSV file

If we want our Microsoft Excel to read a CSV file with the field separator which are used in the given CSV file so in that case, we can easily specify the separator directly in that file.

And for this, we will open our file in any text editor. Let's say we are opening the Notepad and then move on to typing the below string before any other data respectively:

1. Separating the values with a comma: sep=;
2. Separating the values with a semicolon: sep=;
3. Separating of the values with a pipe: sep=|

Furthermore, similarly, we can make use of any other character for the delimiter.

And once the delimiter is defined, we can open our text file in Microsoft Excel like we normally would, from Excel itself or Windows Explorer respectively.

How to change the default CSV separator globally in Microsoft Excel?

And if we want to change the default List separator not only for Excel but for all programs which are primarily installed on our system or the computer and to achieve the above we have to do the following mentioned things effectively:

Step 1: On the current Windows, we will move to the "Control Panel > and then to the Region settings. And for this, we will type out the Region in the Windows search box and clicking on the Region settings efficiently:

Step 2: Then, in the Region panel, which is present under related settings, we will click on the Additional date, time, and regional settings.

Step 3: And now, in these steps just under the Region option we will be clicking on the Change date, time, or number formats respectively:

Step 4: Now after that, in this particular Region dialogue box, which appears in the Formats tab, we will click on the Additional settings.

Step 5: Now, after that, in the Customize Format dialogue box, which is present under the Numbers tab, we will type the respective character which we want to make use of it as the default CSV delimiter in the given List separator box respectively.

Step 6: After that, we will click on the "OK "twice to close both dialogue boxes effectively. And once we are done with it, we will restart Microsoft Excel so that it can efficiently pick our changes.

Changing the List separator: background and consequences

Now in this concept, before making any changes in the List separator on our system I will ask you all to go through the section below carefully so that you can easily understand the possible outcomes very well.

First, the different countries use the other default separators as it depends on the 'additional countries' Windows.

This is because vast numbers are primarily written in various ways across the globe or the world.

1. For example: In some of the English speaking countries such as: UK, New Zealand etc., makes use of the below separators:

List Separator: comma (,).

Digit grouping symbol: comma (,).

Decimal symbol: dot (.).

1. For example: If we are considering or taking out the European countries, we use the semicolon as their default list separator. This is because the comma is utilized as a decimal point.

Digit grouping symbol - dot (.)

Decimal symbol - comma (,)

List separator- semicolon (;)

As per the above figure, we can see that two thousand dollars and fifty cents is basically in different style in different countries:

1. US: $2,000.50.
2. EU: $2.000, 50.

So the question is that: How does all this relate to the CSV delimiter?

The point is that the particular List separator (CSV delimiter) and the Decimal symbol should be considered the two different characters, meaning that setting the List separator to a comma will require changing the default Decimal symbol. And as a result, the numbers will be displayed differently in all our applications respectively.