This post was most recently updated on January 19th, 2023.
6 min read.This article will explain how you can change the Excel list delimiter for your CSV (or other) file exports. Because it isn’t always a comma. And setting the delimiter won’t escape or strip the same characters in the values, so it’ll mess up your data quite easily.
Recently, I needed to find a way to import data from an old Excel file to a custom-coded system that could import CSV files. However, the data was fairly non-sanitized – containing commas, semicolons, and other weird characters. I could define the delimiter used in the imported data – however, that would be useless if I wasn’t also able to define it in the export.
With Excel, it’s not that simple, though. Excel, just like other Microsoft Office applications loves trying its best to respect the regional & localization settings of the machine it’s running on – most often to frustrating results.
However, like with so many other things, you can change the delimiter value used in CSV export by tweaking these settings. This way, you can change the commas to semicolons in Excel’s CSV export, or whatever else you need.
And this article describes how!
Another day, another workaround. If you’re not interested in the background stuff and WHY I ran into this issue, just click to jump right down to the actual solution:
How to export a CSV with a custom delimiter using Microsoft Excel
Background
If you’re still here, let’s delve into the background of the issue a bit deeper.
I needed to wrangle data stored in a .xlsx file into another form, that another application could read. This file format happened to be CSV. Which brought me into the beautiful world of CSV delimiter configuration in Windows.
What’s a “CSV” -file ?
Stands for “comma-separated values” – A text file that uses a comma to separate values.
Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator (Delimiter) is the source of the name for this file format.
Normally, that’s simple – just “save as” with a different file format. However, this time I was posed with an issue: the data included both commas (“,”) and semicolons (“;”), both of which are typical delimiters in CSV files. Additionally, I couldn’t wrap the values inside quotes due to limitations in both the export and the import functionality. So I needed to use another character – such as pipe (“|”).
Instead of being able to configure the delimiter in the export (like in any sensible spreadsheet editor, if you ask me!), Excel is just going to use your regional settings and grab the list separator from there. Nice and automatic – probably someone thought this to be user friendly, but instead it makes edge cases a pain.
This means that in English, CSV is going to be values limited by commas, like this:
Value1,Value2,Value3,Value4...But in Finnish, for example, we’ll be using the semicolon – like so:
Value1;Value2;Value3;Value4...And I could imagine there are plenty of other locales where you might need to tell the values apart with a semicolon instead of a comma. Or you might even need something totally different!
But simply changing my region to something else isn’t a solution this time. The data I had been supplied had both colons and semicolons in cell values. The data would be incorrectly split and parsed due to that.
However, we can luckily override the delimiter to be almost anything – and it’s all done through the regional settings. Read on to see for yourself!
Solution
So, I started googling. And naturally, I found a couple of examples on how to save a csv file with a different delimiter in Windows 7… Obviously, that’ll help you eventually find your way in Windows 10 as well, even though the user interfaces are a bit different. So I thought it’s always useful to also document the exact steps!
The guide below should be accurate for Windows 10 and 11, and be “close enough” for Windows 7. :)
Here are the steps in writing – see some useful screenshots further below!
Time needed: 10 minutes.
How to export a CSV with a custom delimiter using Microsoft Excel?
- Make sure Microsoft Excel is closed before trying to change the CSV delimiter
Making sure Excel is closed is required to make sure it picks up your changes.
- Open Control Panel
Opening the Control Panel can be done by hitting win+r and writing “Control Panel” in the Run window.
Or by searching for “Control Panel” in your start menu. Whichever suits you :) - Next, you need to access Regional Settings
Now you’ll need to navigate to Regional Settings. This is where you can find it:
It’s available either under “Region” or “Clock and Region” -> “Region”. - Click the “Additional settings” -button
Hit that “Additional settings…” -button now! This will pop up quite a few additional options – in hindsight, not that surprising, but it’s always nice when a button does what you expect it to do.
- Find the “List separator” and change it to your preferred delimiter such as a pipe (“|”).
You’ll need to edit the “List separator” now.
While the dropdown might have some prepopulated values, you can actually just safely ignore them and input whatever delimiter you need to use to the text field directly.
Note, that there’s at least one, undocumented limitation here: you can’t use the same character as both decimal and list separator! This means that if you’re using regional settings like Finnish, where a comma is considered to be a decimal separator instead of a full stop or “dot”, you can’t use it as a list delimiter…
Before you change the decimal separator to something else, that is :)
This also doesn’t come without some side effects: the same list separator is used, for example, as the separator for passing parameters to an Excel function, as Faz notes in the comments section below. - Click OK -> OK -> exit Control Panel, after which you can proceed to actually export the file!
With the selection done, just hit “OK” a couple of times and head back to Excel!
You should be able to save your file as CSV or use the Export function – and you should be good this time. - Open the Excel file
Open the file you want to export to a CSV with a custom delimiter (in my example, to a “pipe-delimited file”).
- Select File -> Save As
“Saving as…” should pop up the classic saving dialog – which in Excel comes with some useful options.
- Change the ‘Save as type’ to ‘CSV (Comma delimited)(*.csv)’
Changing to “CSV (Comma delimited)” is admittedly a bit misleading at this point, but we’ll just have to live with that.
- Change the name and file extension if need be
You can modify the extension and the file name, but by default, it stays as csv even if you’re using a different delimiter.
- Click Save -> OK -> Yes
After hitting a couple of extra affirmative buttons, you should be good!
As an interesting side note, you can use completely nonsensical characters as delimiters! Take the picture below as an example – I actually needed this delimiter for a work project once. A project, that’s probably still running in production…
If you search for this online, you’ll get the easy answer – a comma. It’s “comma-separated values” after all, right?
Well, as usual, the easy answer is wrong. You can even find it on support.microsoft.com. But it’s still the wrong answer.
The default delimiter is defined by your Windows locale. It’s usually either a comma or a semi-colon, but it’s not necessarily one of those. It can be something different, too. Just so you know.
Because Microsoft wants to implement localization everywhere but doesn’t actually care about people using any other locales than EN-US. 😅
References
Big kudos to Barry Stevens for his blog post on the topic! And also to Sami Laiho, for noticing you can’t use the same character as both decimal and list separator 😬..
He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Office Development.
This is his personal professional (e.g. professional, but definitely personal) blog.
- How to fix disconnected Sonoff Zigbee sensors in Home Assistant? - January 24, 2023
- How to fix all UWP/WinUI apps being laggy in Windows 10/11? - January 17, 2023
- How to export the SSL/TLS certificate from a website using PowerShell? - January 10, 2023
