Specifying delimiter for Excel 2007 export

Question

I'm trying to save an Excel 2007 file as a pipe-delimited text file but in the Save As list, I see only tab-delimited and csv options. How do I go about specifying the delimiter, which as I recall was possible in earlier Excel versions via a wizard?

Answer 1

From this page:

• Bring up your default options window by clicking Start -> Settings -> Control Panel -> Regional Settings.
• Click the "Customize" button.
• Click the “Number” tab and in the “List Separator” field, replace the current default separator with the one you want to use (in your case, the pipe symbol).
• Click “OK” to save the change and close the window.

Now exporting as CSV in Excel will produce pipe-delimited files.

Answer 2

Also, assuming no commas in the actual data, you can export to regular .CSV, and then use notepad to Find/Replace all commas with pipes. If you do have commas in your data, then before exporting to .CSV, Find/Replace (within Excel) all of the commas to something that is not used in your data (e.g., the left curly brace, or the tilde). Then, after exporting and find/replacing commas to pipes, you can find/replace that new character back to commas.

Answer 3

Changing the list separator back and forth is a pain.

Leaving it as | is not acceptable as it screws up a lot of things. Saving as a TAB-separated text file, then using a text editor to globally replace the TABs with |s works well.

To do this with Notepad, highlight a TAB character in the text file, then copy it, then invoke Replace, then paste it into the search for box, then put whatever delimiter you want in the replace with box.