CSV Custom Delimiter Excel VBA: Save and Export Files With Any Separator You Need

Excel’s built-in “Save As CSV” has one stubborn limitation: it always uses your Windows list separator — a comma in the US, UK, and India, a semicolon in much of Europe — and gives you no way to pick anything else. That breaks the moment a client, a bank, or a government portal demands a specific format. A csv custom delimiter excel vba macro solves this cleanly: a short script that walks your worksheet row by row and writes a text file using whatever separator you choose — semicolon, pipe, tab, caret, anything. No add-ins, no paid tools, and it works in every desktop Excel from 2010 through Microsoft 365. In this guide you’ll get a complete copy-paste macro, a plain-English explanation of how it works, variants for semicolons and pipes, proper quoting and UTF-8 handling, and a batch version that exports dozens of files in one click.

This is a practical, code-first guide. If you’ve never opened the VBA editor before, don’t worry — the installation takes two minutes and every step is spelled out. If you only need a one-off conversion rather than a reusable macro, the free CSV to Excel converter on our homepage handles the import side; the macro below is for when you need precise control over the export side, again and again.

Why Excel refuses to let you choose a CSV delimiter

To understand the fix, it helps to know why the problem exists. When you choose File › Save As › CSV in Excel, Excel doesn’t ask which character separates your fields — it silently uses the list separator from your operating system’s regional settings. On US, UK, Indian, Canadian, and Australian systems that’s a comma; on German, French, Spanish, Italian, and Brazilian systems it’s a semicolon (because those locales use the comma as a decimal separator). There is no dropdown, no checkbox, no advanced option — the choice was made for you by your locale.

This causes real friction. A supplier in Mumbai sends a template expecting pipe-delimited files. A German tax portal rejects comma-delimited uploads. A legacy database import chokes on anything but tabs. The usual workarounds are all flawed: changing your Windows regional list separator affects every program on your PC and confuses colleagues; doing find-and-replace on commas corrupts data that legitimately contains commas (like "Acme, Inc."); and asking the recipient to “just deal with commas” doesn’t fly when their system validates the format. Microsoft documents the underlying import/export behavior in its text file import/export guide, which confirms there’s no built-in delimiter choice on export.

The VBA approach sidesteps all of this. Instead of relying on Excel’s Save As, the macro reads cell values itself and writes the text file character by character, so the delimiter is just a variable you set. It also handles the quoting rules correctly — something the find-and-replace workarounds never do. The official VBA reference for Excel is the authority if you want to extend the code further, but the macro below is complete as-is.

Excel VBA: save a CSV with a custom delimiter — the complete macro

Here is the full macro. It exports the active worksheet, asks you where to save, and writes every row using the delimiter you set in one clearly-marked line. Copy it exactly as shown.

Sub ExportCSVCustomDelimiter()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim line As String, delim As String
    Dim fNum As Integer, fPath As Variant

    delim = ";"   ' <-- YOUR DELIMITER: change to "|" or vbTab etc.

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    fPath = Application.GetSaveAsFilename(FileFilter:="Text Files (*.csv), *.csv")
    If fPath = False Then Exit Sub

    fNum = FreeFile
    Open fPath For Output As #fNum
    For r = 1 To lastRow
        line = ""
        For c = 1 To lastCol
            line = line & QuoteField(ws.Cells(r, c).Value, delim)
            If c < lastCol Then line = line & delim
        Next c
        Print #fNum, line
    Next r
    Close #fNum
    MsgBox "Exported with """ & delim & """ delimiter.", vbInformation
End Sub

Function QuoteField(val As Variant, delim As String) As String
    Dim s As String
    s = CStr(val)
    If InStr(s, delim) > 0 Or InStr(s, """") > 0 _
        Or InStr(s, vbCr) > 0 Or InStr(s, vbLf) > 0 Then
        s = Replace(s, """", """""")
        QuoteField = """" & s & """"
    Else
        QuoteField = s
    End If
End Function

How the macro works, line by line

The macro finds the last used row and column so it never writes the phantom empty columns that cause trailing-comma problems. It then loops through every cell, passing each value through QuoteField — a helper that wraps a value in double quotes (doubling any quotes inside) whenever the value contains the delimiter, a quote, or a line break. This is exactly what RFC 4180 requires, and it’s the step that separates a correct export from a corrupted one. Each finished row is written with Print #, which adds the line break.

Installing the macro (2 minutes)

1. Press Alt+F11 to open the VBA editor.
2. Go to Insert › Module to create a blank code module.
3. Paste both the Sub and the Function into the module.
4. Press Ctrl+S. If prompted, save your workbook as .xlsm (macro-enabled).
5. To make the macro available in every workbook, store it in your Personal Macro Workbook (record any dummy macro with “Store in: Personal Macro Workbook” once, then paste the code into the PERSONAL.XLSB module).
6. Run it with Alt+F8, select ExportCSVCustomDelimiter, and click Run.

Excel VBA save CSV with semicolon: adapting the macro

The most requested variant is the semicolon export — needed for German, French, and other continental-European systems where the comma is a decimal separator. With the macro above, an Excel VBA save CSV with semicolon workflow is literally a one-character change: the delim = ";" line already uses a semicolon. Run it as-is and your file comes out like Priya;29;Mumbai, ready for any European portal or ERP import.

Two details matter for semicolon files. First, values containing semicolons (rare, but they happen in free-text notes fields) get quoted automatically by QuoteField — verify with a quick eyeball of the output. Second, if the recipient opens the file in their Excel, their locale will parse semicolons correctly by default; if they open it in a US-locale Excel, they’ll need Data › From Text/CSV and a manual delimiter pick. Mention the delimiter in your email when you send the file — a one-line note saves a round of “your file is broken” replies.

VBA export CSV with pipe delimiter (and tabs, carets, tildes)

A VBA export CSV with pipe delimiter setup is just as easy: change the delimiter line to delim = "|". Pipe-delimited files (sometimes called PSV) are popular with data warehouses and ETL tools because the pipe character almost never appears inside real data — unlike commas, which show up in company names, addresses, and numbers. Tabs work the same way with delim = vbTab (VBA’s built-in tab constant), and exotic separators like carets (^) or tildes (~) are single-character swaps.

One caution for tabs: if you name the output .csv but use tab separation, some systems will misread it. Save tab-delimited output with a .txt or .tsv extension to avoid confusion — the macro’s save dialog lets you type any filename. And if your data might contain the pipe character itself (log files sometimes do), the quoting helper still protects you, but consider a rarer delimiter like ¤ or a multi-character sequence for maximum safety.

Handling quotes, line breaks, and UTF-8 in your VBA export

Delimiter choice is only half the battle — the other half is making sure tricky cell contents survive the trip. The macro’s QuoteField helper already covers the two classic cases. Embedded quotes: a value like Say "hi" becomes "Say ""hi""" — quotes doubled, whole field wrapped — which every CSV parser understands. Line breaks: multi-line cells (Alt+Enter in Excel) are wrapped in quotes so the break doesn’t create a phantom new row. What the helper deliberately does not do is quote every field; quoting only when needed keeps files small and readable.

UTF-8 needs one extra step. VBA’s Open...For Output writes in your system’s ANSI encoding, which mangles characters like é, ü, ₹, or 中文 on systems with a different default code page. For an export that must be UTF-8 (increasingly the norm for web uploads and APIs), replace the file-writing lines with an ADODB.Stream:

Dim stm As Object
Set stm = CreateObject("ADODB.Stream")
stm.Type = 2 ' text
stm.Charset = "utf-8"
stm.Open
' ... inside your row loop, instead of Print #fNum, line:
stm.WriteText line & vbCrLf
' ... after the loop:
stm.SaveToFile fPath, 2
stm.Close

This writes a proper UTF-8 file that opens correctly everywhere — US, European, and Indian systems alike. If the receiving system specifically wants a UTF-8 BOM (some older Windows tools do), write the three BOM bytes first or check its import docs.

Dates and numbers: export values, not formats

The macro exports .Value — the underlying value, not the displayed format. A cell showing ₹1,250 exports as 1250; a date showing 05-Oct-2026 exports as its serial number or locale date depending on settings. If you need the displayed text instead, change .Value to .Text in the loop — but be aware .Text fails on error values, so it’s a trade-off. For financial exports where formatting matters, export values and let the receiving system format them.

Batch export: run the macro on every sheet or file at once

One sheet is nice; fifty is where the macro earns its keep. This batch version loops through every worksheet in the workbook and exports each to its own delimited file, named after the sheet:

Sub BatchExportAllSheets()
    Dim ws As Worksheet, orig As Worksheet
    Dim delim As String, folder As String
    Set orig = ActiveSheet
    delim = ";" ' your delimiter here
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show <> -1 Then Exit Sub
        folder = .SelectedItems(1) & "\"
    End With
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
        ExportSheetToFile ws, folder & ws.Name & ".csv", delim
    Next ws
    orig.Activate
    Application.ScreenUpdating = True
    MsgBox "All sheets exported.", vbInformation
End Sub

Refactor the original export loop into ExportSheetToFile(ws, fPath, delim) — a small edit — and the batch driver above calls it per sheet. The same pattern extends to whole folders: loop Dir("C:\Exports\*.xlsx"), open each workbook, run the export, close. An accountant in London running month-end for forty cost centres, or a logistics coordinator in Mumbai exporting daily manifests, turns an hour of clicking into one button press. Pair this with Windows Task Scheduler and the workbook opens, exports, and closes on a schedule — a genuinely hands-free pipeline built from free parts.

VBA vs Power Query vs Python for custom-delimiter exports

VBA isn’t the only route — pick by context. Power Query can write delimited text only indirectly (it exports via Excel’s CSV, inheriting the locale separator), so it’s a poor fit for custom delimiters; use it for import-side cleanup instead. Python with the csv module is superb — csv.writer(f, delimiter='|') — and better than VBA for scheduled server jobs or files over a few hundred megabytes, but it requires Python installed and some scripting comfort. VBA wins when the workflow lives inside Excel: the data is already there, the users already have Excel, and the macro travels with the workbook. Our CSV to PDF guide covers the opposite direction — turning finished data into shareable documents — for when the export’s final audience is human rather than a system.

There’s also the “change Windows list separator” trick (Control Panel › Region › Additional settings). It makes Excel’s native Save As CSV use your chosen character with zero code. It works, but it changes the behavior of every application on the PC and confuses anyone who opens your files expecting standard commas — fine for a personal machine, risky on shared or corporate PCs. The macro is the surgical option: it changes one export, not the whole computer.

Put the macro one click away: ribbon button and keyboard shortcut

A macro you have to hunt for through Alt+F8 won’t get used. Pin it where your hands already are. The fastest option is the Quick Access Toolbar: right-click the toolbar at the top of Excel, choose Customize Quick Access Toolbar, change the “Choose commands from” dropdown to Macros, select ExportCSVCustomDelimiter, and click Add. Give it a friendly icon — the green spreadsheet glyph works well — and the export is now one click away in every workbook.

For a keyboard shortcut, open the macro list with Alt+F8, select your macro, click Options, and assign a letter — Ctrl+Shift+E is memorable for “export.” Avoid overwriting Excel’s built-ins (don’t take Ctrl+C’s territory). If you stored the macro in PERSONAL.XLSB, the toolbar button and shortcut follow you across every workbook on that PC, which is exactly what you want for a daily export routine. Teams can go one step further and distribute the macro in a shared .xlam add-in placed on a network drive: everyone who installs the add-in gets the same one-click export with the company’s required delimiter baked in, and when the format spec changes, you update one file instead of forty. That turns a personal timesaver into a small piece of team infrastructure.

Troubleshooting your delimiter macro

“Compile error: Sub or Function not defined.” You pasted only the Sub without the QuoteField function — both must be in the module.

The macro is missing from Alt+F8. It’s in a module of a different workbook, or you saved as .xlsx (which strips macros). Save as .xlsm or put it in PERSONAL.XLSB.

Output has commas instead of my delimiter. You edited a copy of the macro but ran the old one, or the file was re-saved by Excel’s native Save As afterward. Run the macro and check the output file immediately.

Accented characters look wrong. You’re hitting the ANSI limitation — add the ADODB.Stream UTF-8 snippet from the encoding section.

Numbers lost their formatting. The macro exports values, not display formats. Switch .Value to .Text if you need the formatted appearance.

Recipient’s Excel mangles the file. Their locale expects a different delimiter. Tell them the delimiter explicitly, or use Data › From Text/CSV with a manual delimiter choice on their end — the same fix our trailing commas guide recommends for import-side issues.