Pinterest Pixel

How to Change Excel CSV separator

Microsoft Excel is a powerful spreadsheet application widely used for data analysis and manipulation. When working with... read more

Download Excel Workbook
John Michaloudis
Posted on
Excel CSV Separator

Overview

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 WORKBOOK

 

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.

See also  The Best Guide to Edit PowerPoint Videos and Clips

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.

Excel CSV Separator

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

Excel CSV Separator

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

Excel CSV Separator

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

Excel CSV Separator

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 –

See also  Create To Do List in Excel 3 Examples

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

Excel CSV Separator

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

Excel CSV Separator

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.

Excel CSV Separator

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

Excel CSV Separator

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

Excel CSV Separator

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

See also  020: How To Prepare For An Excel Assessment Test For A Job Interview

Excel CSV Separator

 

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.

Excel CSV Separator

STEP 2: Go to Clock and Region.

Excel CSV Separator

STEP 3: Select Region.

Excel CSV Separator

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

Excel CSV Separator

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

Excel CSV Separator

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.

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

See also  CTRL + 1: Format Cells
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!