Note: The other languages of the website are Google-translated. Back to English
English English

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.
doc change-delimiter-when-saving-as-csv 1

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.
doc change-delimiter-when-saving-as-csv 2

2. In the Editing options section, uncheck the Use system separators box.
doc change-delimiter-when-saving-as-csv 3

3. Set the Decimal separator to comma (,) and set the Thousands separator to period (.). Click OK.
doc change-delimiter-when-saving-as-csv 4

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.
doc change-delimiter-when-saving-as-csv 5

6. Open the CSV file you just saved with Notepad. You will see this CSV file is semicolon delimited.
doc change-delimiter-when-saving-as-csv 6

Notes

  1. 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 (,).
    doc change-delimiter-when-saving-as-csv 7
  2. 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 Quickly Convert A Text File To Excel File With Delimiter?
Have you ever tried to convert a text file to an Excel file with delimiters? In this article, I am going to talk about the methods of solving this problem.

How To Split By Delimiter And Transpose Cells In Excel?
In this article, I introduce the ways to split a range by specific delimiter and then transpose it in Excel as below screenshot shown. If you are interested in this task, please go to below details.

How To Split Cells By First Space Delimiter In Excel?
In Excel, you can quickly split cell content separated by comma, space or other delimiter with text to column feature. Here in this article, we will show you how to split cell by first space delimiter in Excel.

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.



  • 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...
kte tab 201905
  • 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!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations