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.
