Saving spreadsheets as delimited text files

R

rwebster3

I have a need to save data from some spreadsheets as a pipe (|) delimited file. I've been able to import data that way, but have yet to figure out a way (short of writing each cell individually) into this format. Anyone know of a way to preserve the | delimiter after reading in the file?
 
M

Mark_K

Some suggestions.

1. In Excel, save the file as "Comma Delimited (*.csv)", then open i
in Notepad, replace all comma characters with pipe characters, and the
save the file as "Text Documents (*.txt)".

2. After editing in Excel, insert a new column A and add a formula t
concatenate each cell from column B onwards, with a pipe character i
between, e.g. =B1&"|"&C1&"|"&D1. Copy this formula for all cells i
column A, then convert these formulas to values before deleting column
B onwards. Save the file as "Text (Tab delimited) (*.txt)". The pip
delimiter will remain.

3. Write a macro to do either of the above
 
D

Dave Peterson

Try changing your windows settings.

In win98:
windows start button|settings|control panel|regional settings applet|
Number tab

At the bottom is a list separator. Change it to the pipe.

Remember that this is a windows property and may affect other programs.
 
Top