Excel change default delimiter of CSV files

This Excel tutorial explains how to change default delimiter of CSV files from comma to other symbols.

You may also want to read:

Excel VBA convert CSV to Excel

Excel change default delimiter of CSV files

When you open a CSV file in Excel, you can see all columns are delimited by comma, you don’t have to define which character you want to use to delimit the row. It is because the default delimiter of comma is configured at Windows level, all Windows applications (including Excel) will use the same default.

It is possible to change the default delimiter.

In Windows 10, navigate to Start > Control Panel > Region > Additional Settings > List Separator

In Windows 7, navigate to Start > Control Panel > Regional and Language Options > Additional Settings > List Separator

Normally speaking, if a field value contains comma (such as thousands separator), you should double quote around field value to avoid mixing up with the comma that is used as delimiter. If that is not the case, semi colon can be used as a delimiter when generating the CSV.

Suppose we have a CSV file that contains 2 numbers: 1000 and 2000.

Open the file in Excel. By default, default delimiter is comma. Since there are 3 commas, the two numbers are delimited into 4 cells.

 

Now edit the CSV file in Notepad, add double quote around each number.

 

Open the file in Excel, now the two numbers are correctly delimited. Note that the currency separator can also be changed in the setting.

 

Example – change default delimiter of CSV files to semi-colon

Let’s say we have a file delimited by semi-colon

 

Change the default delimiter to sem-colon, now open the file in Excel, the two numbers are correctly delimited.

 

 

Wyman W
Manager@IBM
Wyman is human resources professional specialized in implementation of HR information system.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *