3 Ways To Save Excel As Pipe Delimited

Saving Excel files as pipe-delimited text files can be a useful way to transfer data between different systems or applications that support pipe-delimited formats. In this article, we'll explore three methods to save Excel files as pipe-delimited text files.

Method 1: Using the "Save As" Option

The first method is to use the "Save As" option in Excel to save the file as a pipe-delimited text file. Here's how to do it:

1. Open your Excel file and select the data range you want to save.
2. Go to the "File" menu and select "Save As."
3. In the "Save as type" dropdown menu, select "Text Files" (.prn,.txt,.csv).
4. In the "File name" field, enter a name for your file.
5. In the "Save as" dialog box, click on the "Tools" button and select "Web Options."
6. In the "Web Options" dialog box, click on the "Encoding" tab.
7. Select "UTF-8" as the encoding scheme.
8. Click "OK" to close the "Web Options" dialog box.
9. In the "Save as" dialog box, click "Save" to save the file.

By default, Excel will save the file as a comma-delimited text file. To save it as a pipe-delimited text file, you need to specify the pipe character as the delimiter.

Method 2: Using the "Text to Columns" Feature

The second method is to use the "Text to Columns" feature in Excel to convert the data to pipe-delimited format. Here's how to do it:

1. Open your Excel file and select the data range you want to convert.
2. Go to the "Data" menu and select "Text to Columns."
3. In the "Text to Columns" dialog box, select "Delimited text" and click "Next."
4. In the "Delimiters" section, select the "Other" checkbox and enter the pipe character (|) as the delimiter.
5. Click "Finish" to convert the data to pipe-delimited format.
6. Save the file as a text file using the "Save As" option.

Method 3: Using VBA Macro

The third method is to use a VBA macro to save the Excel file as a pipe-delimited text file. Here's how to do it:

1. Open your Excel file and press "Alt + F11" to open the VBA editor.
2. In the VBA editor, insert a new module by clicking "Insert" > "Module."
3. Paste the following code into the module:

Sub SaveAsPipeDelimited()
    Dim filePath As String
    Dim fileNumber As Integer
    Dim i As Integer
    
    filePath = "C:\path\to\file.txt"
    fileNumber = FreeFile
    
    Open filePath For Output As #fileNumber
    
    For i = 1 To Cells.Rows.Count
        Write #fileNumber, Join(Application.Transpose(Application.Transpose(Rows(i).Value)), "|")
    Next i
    
    Close #fileNumber
End Sub

4. Replace "C:\path\to\file.txt" with the desired file path and name.
5. Run the macro by clicking "Run" > "Run Sub/UserForm" or by pressing "F5."
6. The macro will save the Excel file as a pipe-delimited text file.

Gallery of Excel Pipe Delimited Images

Excel Pipe Delimited Images

We hope this article has helped you learn how to save Excel files as pipe-delimited text files. Whether you use the "Save As" option, the "Text to Columns" feature, or a VBA macro, you can easily convert your Excel data to pipe-delimited format. If you have any questions or need further assistance, please leave a comment below.