How to change Excel CSV delimiter to comma or semicolon

This tutorial explains how to adjust CSV separators when importing or exporting data to/from Excel, allowing you to save files in comma-separated or semicolon-separated formats.

Excel's automatic settings can sometimes lead to unexpected results. For instance, you might intend to export data as a comma-separated CSV file, but instead receive a semicolon-delimited file. This tutorial will guide you through changing this setting.

- Understanding Excel's CSV separator

• Saving Excel files as CSV with custom delimiters
• Opening or importing CSV files with custom delimiters
• Globally changing the default CSV separator
• Modifying the List separator: implications and considerations

Excel's CSV Delimiter

Microsoft Excel uses the List separator defined in Windows Regional settings to handle .csv files. In many regions, the default is a comma, creating comma-separated CSV files. However, in others (like many European countries), a semicolon is used as the list separator, resulting in semicolon-delimited CSV files. The methods below show how to override this.

Changing the Separator When Saving as CSV

Excel typically uses the default List separator when saving as .csv. To force a different delimiter:

1. Go to File > Options > Advanced.
2. Uncheck Use system separators under Editing options.
3. Change the Decimal separator. To avoid confusion, also change the Thousands separator to a different character.

To create a semicolon-delimited CSV:

• Set Decimal separator to a comma (,)
• Set Thousands separator to a period (.)

To create a comma-delimited CSV:

• Set Decimal separator to a period (.)
• Set Thousands separator to a comma (,)

Remember: Changes made in Excel Options only affect Excel. Other applications will still use the system's default List separator.

Changing the Delimiter When Importing CSV

Several methods exist for importing CSV files. The delimiter adjustment method depends on your chosen import technique.

Specifying the Separator Directly in the CSV File

To ensure Excel correctly interprets a CSV file's delimiter, add a line at the beginning of the file specifying the separator:

• Comma: sep=,
• Semicolon: sep=;
• Pipe: sep=|

You can use other characters as well. After adding this line, open the file in Excel.

Example for a semicolon-delimited CSV:

Using the Text Import Wizard (Legacy)

For older Excel versions or to access this functionality, you can use the Text Import Wizard:

• Enable the "From Text (Legacy)" feature if necessary.
• Change the file extension from .csv to .txt and open it in Excel. This will automatically launch the wizard.
• In step 2, select your delimiter (tab, comma, semicolon, space, or custom).

Using Power Query

In Excel 2016 and later, Power Query offers another import method. During connection setup, you can specify the delimiter in the Preview dialog.

Globally Changing the Default CSV Separator

To change the default List separator system-wide (affecting all applications):

1. Access Windows Region settings (Control Panel > Region or search for "Region").
2. Click Additional date, time, and regional settings.
3. Click Change date, time, or number formats.
4. Click Additional settings….
5. On the Numbers tab, change the List separator. Ensure it differs from the Decimal symbol.
6. Click OK twice. Restart Excel for changes to take effect.

Caution: System-wide changes affect all applications. If problems arise, use the Reset button in the Customize Format dialog to restore defaults.

Understanding List Separator Changes

Different regions use different default separators (decimal, thousands, list). The List separator and Decimal symbol must be different. Changing the List separator also affects number display and formula argument separators in Excel. If unsure, modify the separator only for individual files.

US/UK: Decimal (.), Thousands (,), List (,)

EU: Decimal (, ), Thousands (.), List (;)

This tutorial details how to manage CSV delimiters in Excel. Choose the method best suited to your needs.

The above is the detailed content of How to change Excel CSV delimiter to comma or semicolon. For more information, please follow other related articles on the PHP Chinese website!