SEARCH

How-To Geek

Export or Save Excel Files with Pipe (or Other) Delimiters Instead of Commas

If you’re working with some awful corporate system that should have been retired in the dark ages, you might have had to produce a file from Excel with some weird delimiters other than comma or tab-delimited to import into the system. Here’s the trick on how to do it.

Naturally, this isn’t the type of article that most people will need to use, hopefully ever—but if you do need to know how to do it, the solution isn’t really obvious at first.

Exporting Excel Files as Pipe Delimited

For the purposes of this example, we’ll use this silly little Excel file.

image

To save the file as Delimited, you’ll need to click the Office button and choose Save As –> Other Formats.

image

Then select CSV (Comma delimited)(*.csv) from the drop-down list, and give it a name.

image

Now here’s where the interesting part happens… Excel by default will use a comma as the delimiter, but if you open up Control Panel –> Region and Language, and then click the Additional settings button on the bottom…

image

Now look very closely at the “List separator” item, which normally has a comma in the field, but for the purposes of illustration I’ve switched it to a Pipe character.

image

Once you hit Apply, and then save your Excel file, you’ll notice that your file now has pipe characters as the delimiter.

image

You’ll probably want to change the List separator back to a comma just in case some other application needs it.

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 07/8/10

Comments (12)

  1. Howard

    I’ve always preferred pipe delimited over comma as commas can appear in the data as data. While this shouldn’t screw things up, some software has been less than exceptional at producing delimited text files (DLT’s).

    Pipe I find inifitely more readable if data tweaking somehow becomes necessary prior to import.

  2. SquareWheel

    Pipe is much clearer anyway. I’m surprised this isn’t default behavior.

  3. Hariks

    In OOo, this could be done in the save as dialog itself. Means, no messing up with control panel items every time you need to change the delimiter character.

    Another tip MS got from FOSS. :)

  4. SquareWheel

    Yes, but Open Office is also incredibly slow.

  5. Matt

    FYI, you will definitely want to switch back to “,” (comma) in the regional settings, because it screws up Access expressions.

    I got a variety of issues, I will spare you the details, but as soon as I switched back to “,” from “|” in the regional settings, Access expressions behaved normally again.

    This is bad development from Microsoft, you can import text files and tweak the delimiter all you want, but no options on export :(

  6. Eric

    Thanks a million!!!

  7. Jovi

    Here, I found a work-around to accomplish this problem using function concatenate:

    1. Create a new sheet.
    2. On sheet2, type =CONCATENATE(“”"”, sheet1!A1,”", “|”"”, sheet1!B1,”"”|”"”, sheet1!C1,”"”") until you get into the last column.
    3. Copy the entire formula to the last row. This will now show the records in PIPE-delimited format.
    4. Copy to notepad and save as txt.

    You now have a PIPE-delimited text file.

    Jovi Salonga

  8. roymond

    You say “some awful corporate system”, but it’s Excel that’s a god awful application. Having basic capabilities like specifying various delimiters should be a standard feature, not something you have to dig for in some ridiculous place via control panels. MS is the problem, not peoples’ business needs.

    That said, thanks for the post!

  9. johanericka

    hi there, i’ve got a problem here
    i have change the list separator in the control panel but there’s no change when i save to csv (i’m using Excel 2010).
    i try to close Ms. Excel (and also restart my computer as well) but there’s no change (still using “;” for separator format )

    anybody help me please ?

  10. olekki

    Thanks man, I needed it today!

    to johanerica: you have to make sure that your decimal symbol and list separator values in settings are different.

  11. Rahul

    Is this work with Office 2003?
    Please confirm.

  12. Ms. H

    Same problem as johanericka
    change the list separator in the control panel but there’s no change when i save to csv it is still using “;” for separator format

Enter Your Email Here to Get Access for Free:

Go check your email!