How to Save an Excel Spreadsheet As Pipe Delimited

It's sometimes useful to save an Excel file as what's called a delimited text file, where each row of the spreadsheet is a line of text and the cells in a row are separated by a character called a delimiter. One option is to use the pipe symbol | as a delimiter, which can be helpful since it doesn't appear in most spreadsheets and won't get confused for a character in one of the cells. You can have a file in Excel export pipe delimited on a Mac or PC.

Microsoft Excel and other spreadsheet programs such as Apple Numbers and LibreOffice Calc have their own custom file formats that they typically use to save and load data. That's often fine for storing files yourself and sending them to other people who also use the same spreadsheet program, but it can sometimes be a challenge to open these files in other programs that can't always parse them perfectly.

One solution to this problem is to save a spreadsheet as what's called a delimited text file, which is simply plain text where each row is on a separate line and individual cells are separated by a delimiter character. Common choices for the delimiter include commas, semicolons, the pipe symbol | and the null character, which isn't visible.

By default, Excel will save delimited text files separated with commas, but you can convert Excel to pipe-delimited text file settings or another delimiter of your choice by changing the settings in your operating system, whether you're on a Windows PC or a Mac.

On Windows, right-click the Start button and click Settings. Then, click Time & Language, followed by Region. Click Additional date, time, and regional settings and then, under the Region options, click Change date, time, or number formats. Click Additional Settings on the Format tab and then, on the Numbers tab, type a | symbol or whatever symbol you want to use in the List separator box and click OK.

If you're on a Mac, click the Apple button in the top left of your screen and choose System Preferences from the dropdown menu. Select Language & Region, then click the Advanced button. Under Number Separators, change the comma for the decimal separator to a pipe or another symbol of your choice, and click OK. Note that the new symbol will not only be used for delimited files but will also be used to separate groups of thousands, millions, etc., in numbers displayed on your Mac, so you may wish to change this setting back after you save an Excel file as pipe delimited.

On either system, you can then use the Save As menu to create a delimited file. Under "Save as type," choose CSV (comma delimited). Excel will use the system delimiter rather than a comma.

Delimited text files are useful because you can open them in essentially any program. You can even open them in a text editor or word processor and simply read them as text, although it can sometimes be difficult to keep track of the different columns if text is different lengths on different lines. Most spreadsheet and database programs can open them, and so can most programming languages. For that reason, delimited text files are often used to send data between programs.

A disadvantage, though, is that they normally don't contain any of the formatting that a normal spreadsheet file in Excel would have, such as fonts, text sizes and colors. If this formatting is important, you may want to consider using another file type to send data back and forth.