How to change default delimiter of CSV file in excel

This article will guide you to change the default delimiter from “comma” to another character that you want.

As you all know CSV means comma-separated values. A CSV file is like a text file which contains data fields separated by comma as shown below.

The need to change default delimiter

When a CSV file is opened in excel application, you can see all the rows get delimited by comma automatically and you get data in the desired format in excel. You don’t have to define the character to delimit the row. It is because excel application uses comma as default delimiter which is configured at Windows level, all Windows applications (including Excel) uses this default delimiter.

But what when your CSV file contains data which is separated by other than comma. The CSV file will be displayed incorrectly while opening in excel. All the data will be displayed in the first column and you will have to use text to column feature to delimit data.

If most of CSV files you work on daily basis contain data which is separated by other than comma, you will have to use text to column each time you open CSV file. This can be avoided by changing default delimiter of system from comma to that character.

Semicolon is the most commonly used separator after comma. If you work mostly on semicolon separated CSV files in your daily routine, you should change your system’s default list separator from comma to semicolon. This will help you open semicolon delimited file in Excel in proper format and after working saving CSV file into semicolon delimited CSV file.

Steps to change default List Separator

Here are steps to change the default list separator of the system to “semicolon”. You can choose whichever character you want to use as the default list separator.

For Windows 10:

1: Open control panel.

2: Click on Region.

3: Click on Additional Setting.

4: In Number Tab, set List Separator to ; ( or whichever character you want)

5: Click OK two times.

For Windows 7:

1: Open control panel.

2: Click on Regional and Language Options.

3: Click on Additional Setting.

4: In Number Tab, set List Separator to ; ( or whichever character you want)

5: Click OK two times.

Side Effects of changing default List Separator

If you change the default list separator from comma to semicolon, it will change the way of working on applications. If you use the formula in excel application, you will need to use “semicolon” as an argument separator in place of “comma”.

Leave a Reply

Your email address will not be published.