How to Change Excel CSV separator

Microsoft Excel is a powerful spreadsheet application widely used for data analysis and manipulation. When working with CSV (Comma-Separated Values) files, you may encounter situations where you need to change the Excel CSV separator from comma to semi-colon.

Let us look at each of these points in detail.

Download the Excel Workbook below to follow along and understand How to Change Excel CSV Separator – download excel workbookExcel-CSV-Separator.csv

 

Understand Default Excel CSV separator

Before we delve into the different methods to change the Excel CSV separator, let us first understand what delimiter Excel uses for CSV files.

In the USA, UK, and other English-speaking countries, a comma(,) is used as a thousand separator, and a dot(.) is used for decimal points. In European countries, a dot(.) is used as a thousand separator and a comma(,) is used for decimal points.

For example –

USA and UK: 123,456,789.00

Europe: 123.456.789,00

The delimiter CSV uses as a separator should be different from the symbol used to separate thousands. So, in European countries, the list separator is set to a semicolon and you may need to change it to a comma.

This article will  further guide you through the different methods for changing the CSV delimiter in Excel –

Change Separator in Excel Setting

To change the Excel CSV separator to a comma, follow the steps below –

STEP 1: Go to the File > Options.

STEP 2: In the Excel Options dialog box, select Advanced.

STEP 3: Under Editing Options, uncheck the Use system separators check box.

STEP 4: Set the Decimal separator to dot(.) and the Thousand separator to comma(,).

This configuration ensures that the Excel file is saved as CSV with a comma delimiter, as the decimal separator is set to a dot (.).

 

Indicate Separator in CSV file

To specify the separator directly in the CSV file, follow the steps below –

STEP 1: Right-click on the file and select Open With > Notepad.

STEP 2: Type this text at the beginning: sep=,

STEP 3: Press Ctrl + S to save the file.

By explicitly indicating the separator (in this case, a comma), Excel will recognize and adhere to it upon opening the CSV file. Users can replace the comma with their desired separator, such as a semi-colon, dot, etc.

 

Choose Delimiter in Text Import Wizard

An alternative method for managing a CSV file that uses a custom delimiter is to import the file instead of simply opening it. Follow the steps below to change the Excel CSV separator –

STEP 1: Change the extension of the file from .csv to .txt

STEP 2: Go to the Data > From Text/CSV.

STEP 3: Select the .txt file and click on Import.

STEP 4: Select the semicolon as the delimiter and click on Load.

This method seamlessly imports the data into Excel with the specified delimiter.

 

Change Separator in Window Setting

To change the Excel CSV separator for all programs in your computer instead of just Excel files, follow the steps below –

STEP 1: Open the Control Panel on your computer.

STEP 2: Go to Clock and Region.

STEP 3: Select Region.

STEP 4: In the dialog box, under the Formats tab click on Additional Setting.

STEP 5: In the Numbers tab, modify the List separator field.

By making this adjustment at the system level, Excel will interpret separators based on the modified settings.

 

Conclusion

Whether you prefer changing settings in Excel, indicating the separator in the CSV file, utilizing the Text Import Wizard, or adjusting Windows settings, these methods provide flexibility for handling CSV delimiters based on your specific needs. Mastering these techniques ensures efficient data manipulation and analysis in Excel.

Learn more about Formulas with our 101 Advanced Excel Formulas & Functions Examples.