How to change delimiter when saving Excel file as CSV?
CSV stands for comma-separated values, which means a text file that uses a comma to separate values. But a CSV file also can use a semicolon character as a list separator. Sometimes, the comma-delimited file is not what you want, instead, you want a semicolon-separated file. Here in this tutorial, we will talk about how to change the file separator while exporting Excel file as CSV.
Change delimiter when saving Excel file as CSV
In countries such as the USA and UK, the default list separator is a comma, so the CSV file is comma delimited. While in many European countries, the semicolon is used as a default list separator because the comma is set to be the decimal symbol. It means the CSV file is usually semicolon delimited in Europe. To change the delimiter when saving Excel file as CSV, please do as follows.
Please note that in our example, the default list separator is a comma.
1. Open Excel. To change the list separator from comma to semicolon, click File > Options. The Excel Options dialog pops up. Click the Advanced option.
2. In the Editing options section, uncheck the Use system separators box.
3. Set the Decimal separator to comma (,) and set the Thousands separator to period (.). Click OK.
4. Open the Excel file you want to save as CSV. Click File > Save As.
5. The Save As dialog box pops up. Change the Save as type to CSV (Comma delimited) in the drop-down list.
6. Open the CSV file you just saved with Notepad. You will see this CSV file is semicolon delimited.
- If you want to change the list separator back to comma, you can check the Use system separators box. Or set the Decimal separator to period (.) and set the Thousands separator to comma (,).
- In European countries, to change the delimiter from semicolon to comma, you also should uncheck the Use system separators box. Then set the Decimal separator to period (.) and set the Thousands separator to comma (,).
Other Operations (Articles)
How To Split Text By Space/Comma/Delimiter In Excel?
If you have a column list of data and you want to split them into several columns by a specific delimiter just like the below screenshots shown, how can you split them in Excel? Maybe some of users think of the Text to Column function only, but now I will introduce not only Text to Columns function, but also a VBA code for you.
The Best Office Productivity Tools
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by
50%, and reduces hundreds of mouse clicks for you every day!