How to save or convert an Excel file as/to pipe delimited file?
While it is common and straightforward to convert an Excel workbook to a CSV file, you may sometimes need to export your data using a different delimiter, such as the pipe character (|). Pipe delimited files are particularly useful when sharing data with systems or teams that require this format for import or further processing. In this tutorial, you will find various practical methods for saving or converting an Excel file to a pipe delimited file. Each solution is explained in detail, with highlights on their best use cases, potential limitations, and helpful operational tips to reduce errors.
Save an Excel file as pipe delimited file by changing system list separator
VBA Code - Automate saving Excel data as a pipe delimited file without changing system settings
Save an Excel file as pipe delimited file by changing system list separator
Excel's default settings export CSV files with commas as delimiters. However, if you need a pipe (|) delimited file, you can change your system list separator to a pipe character. This method is most suitable for local, one-time exports when you have permission to temporarily change your system's regional settings. Please note that altering the system delimiter will affect all delimiter-dependent operations system-wide until you revert the settings.
To save an Excel workbook as a pipe delimited file, follow these steps:
1. Open the Control Panel on your computer. In the Control Panel window, select Category from the View by drop-down list, then click the Clock, Language, and Region option as shown below.
2. In the Clock, Language, and Region window, click Region (or Region and Language) on the right side. See the screenshot below.
3. In the Region (or Region and Language) dialog box, click the Additional Settings… button as shown.
4. In the Customize Format dialog box, remove the comma in the List separator box under the Numbers tab. Then, press Shift + \ to enter the pipe character (|) as the list separator. Click OK to confirm the new delimiter.
5. Click OK to close the Region (or Region and Language) dialog box and apply the changes.
6. Open your workbook and navigate to the worksheet you want to save as a pipe delimited file. Go to File > Save as > Browse.
7. In the Save As window, perform the following:
7.1) Select the destination folder for your pipe delimited file.
7.2) Enter your desired file name in the File name box.
7.3) In the Save as type drop-down menu, select CSV (Comma delimited). Despite the name, this setting uses the system list separator, which you previously changed to a pipe (|).
7.4) Click Save to export the worksheet.
8. When prompted about possible features not compatible with CSV format, click OK. Please be aware: only the current worksheet will be saved; if your workbook has multiple sheets, you must repeat this process for each, giving each file a unique name to prevent overwriting.
Your data is now saved in a pipe delimited format. To verify the delimiter or to view the exported data, right click the output file, select Open with > Notepad (or another text editor), and review the result as shown below. Each value should be separated by a pipe character (|):
The preview in Notepad should show values separated by the pipe delimiter as demonstrated below.
Notes and best practices: Remember to restore your original system list separator after completing your export to avoid unintended effects in other applications or data processes. This method may not be available if you lack sufficient system privileges, or if changing the system locale could disrupt other programs.
As an alternative, especially in shared or restricted environments, consider using the VBA or formula methods discussed below. These approaches allow you to bypass system settings and maintain flexibility when automating or customizing the data export process.
VBA Code - Automate saving Excel data as a pipe delimited file without changing system settings
If you prefer not to modify your system’s regional settings, you can use a VBA macro to export your Excel data as a pipe delimited file directly. This method is highly efficient for recurring exports, automated workflows, or when operating in environments where changing default delimiters is not permitted. It provides flexibility for saving any worksheet as a delimited text file, and you can easily customize the output.
Advantages: No need to change global system settings; ideal for batch operations and repeated use; customizable for different delimiters and file names.
1. Go to Developer Tools > Visual Basic to open the VBA editor. In the Microsoft Visual Basic for Applications window, click Insert > Module, and paste the following code into the module:
Sub ExportPipeDelimitedFile()
Dim ws As Worksheet
Dim filePath As String
Dim rng As Range
Dim rowArr As Variant
Dim rowStr As String
Dim i As Long, j As Long
On Error Resume Next
Set ws = ActiveSheet
filePath = Application.GetSaveAsFilename(InitialFileName:=ws.Name & ".txt", FileFilter:="Text Files (*.txt), *.txt", Title:="KutoolsforExcel")
If filePath = "False" Then Exit Sub
Set rng = ws.UsedRange
Open filePath For Output As #1
For i = 1 To rng.Rows.Count
rowArr = rng.Rows(i).Value
rowStr = ""
For j = 1 To rng.Columns.Count
rowStr = rowStr & rowArr(1, j)
If j < rng.Columns.Count Then
rowStr = rowStr & "|"
End If
Next j
Print #1, rowStr
Next i
Close #1
MsgBox "Export complete!", vbInformation, "KutoolsforExcel"
End Sub2. Close the VBA editor. Back in Excel, press Alt + F8 to open the Macro dialog, select ExportPipeDelimitedFile, and click Run. A dialog prompts you to specify the save location and file name. After execution, the selected worksheet's data is exported as a pipe delimited text file—no system changes required.
Practical tips:
- This macro creates a text file with data from the active worksheet. To export a specific worksheet, make sure it is active before running the macro.
- Empty cells are exported as empty fields.
- You can adjust the delimiter ("|") in the code for other custom separators if needed.
- If you encounter permission errors, check the file path or ensure you have write access to the target folder.
Excel Formula - Use formulas to create a helper column concatenating values with pipe separators, then export or copy results manually
An effective way to create a pipe delimited version of your data without adjusting any system or Excel global settings is to use a formula in a helper column. This approach is especially suitable for small to medium datasets, one-time exports, or when you want to review the delimited data before exporting.
Advantages: No need for administrator privileges or macro use; quick visual verification possible.
Limitations: Requires manual copying or exporting, not ideal for large datasets or frequent updates.
Suppose your data range is in columns A, B, and C. To concatenate these columns with the pipe delimiter, use the following method.
1. In the first row of your helper column (e.g., D2), enter this formula:
=A2 & "|" & B2 & "|" & C22. Press Enter to apply the formula. Next, drag the fill handle down to fill this formula for all relevant rows. Adjust the formula if your data spans more columns or rows.
Explanation: Each cell in column D will display the row’s data joined by pipe characters. For larger ranges, update the formula to include more columns or use TEXTJOIN (available in Excel2016 and later):
=TEXTJOIN("|",TRUE,A2:C2)This version automatically handles any number of columns and skips empty cells if desired.
3. Once you have concatenated all rows, select and copy the entire helper column, then paste the values into Notepad or another text editor. Save the result as a .txt file for your pipe delimited data export.
Practical tips:
- Check for accidental trailing pipes in formulas if expanding to more columns.
- To use the result in other applications, right-click and copy only the needed column, then paste values (not formulas) into a new Excel sheet or text editor.
- Formulas update dynamically if your data changes—copy as values to freeze exported results.
- If your data contains pipe characters, verify with your downstream system that this will not cause issues in reading the file.
Summary suggestion: The formula helper column method is quick and flexible for ad-hoc or preview exports. It’s best suited for moderate datasets or when you wish to visually inspect results before manual file creation.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in