Save Excel as Pipe Delimited
Saving an Excel file as a pipe-delimited text file involves converting spreadsheet data into a format where each cell is separated by a pipe character |. This format is particularly useful for data interchange between applications that do not support Excel’s native file formats. Here’s a comprehensive guide to achieve this conversion.
Step 1: Prepare Your Excel Spreadsheet
First, ensure your Excel spreadsheet is organized and clean. This means:
- Removing any unnecessary rows or columns that you do not want to include in the final pipe-delimited file.
- Checking for and correcting any errors in your data.
Step 2: Open the Save As Dialog
Go to the File menu in Excel. Click on Save As and then choose Browse to select a specific location to save your file.
Step 3: Choose the File Format
From the Save as type dropdown menu, select Text (Tab delimited) (*.txt) or CSV (Comma delimited) (*.csv) if the tab-delimited option is not available. Excel does not directly offer a pipe-delimited save option, but this step is crucial for setting up the correct encoding.
Step 4: Modify the List Separator (Windows)
For Windows users, you need to change the system’s list separator to a pipe character:
- Open the Control Panel and navigate to Clock and Region.
- Click on Region.
- In the dialog that appears, go to the Formats tab and click on Additional settings….
- Change the List separator from its current value to the pipe character |.
This step changes the system-wide list separator setting, affecting how applications, including Excel, separate list items. Remember to change it back if needed.
Step 5: Save the File
Return to Excel and proceed to save your file. With the modified list separator setting, click Save in the Save As dialog. Excel will warn you about features not compatible with the chosen format.
Step 6: Restore Your List Separator (Optional)
If you changed your system’s list separator to a pipe, remember to revert it back to its original setting (usually a comma or semicolon) by following the same steps outlined in Step 4.
After saving your file, it’s a good practice to open it with a text editor (like Notepad) to verify that the data is correctly delimited by pipes.
