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 –
Table of Contents
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
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.
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.