How can I save Excel am spreadsheet as pipe delimiter?

[Sarah]

Can anyone tell me if it's possible to save an Excel spreadsheet as a pipe
delimiter instead of a tab or comma separated?

[Dave O]

I don't think it can be done directly- but if you save it as comma
delimited, you could open it in Word or WordPad or some other text
editor, and do a search and replace.

Or you could write some code that reads each line of the file, replaces
commas with pipes, and rights that info to another file. Not too
tricky.

[Dave Peterson]

One way is to change that list separator.

Under the Regional settings Applet under Windows control panel.

In winXP, you can click on the Advanced button on the "regional options" tab.

Then on the numbers tab, change the list separator character.

Remember to change it back when you're done. It's a windows setting and affects
other programs.

Another option is to write a macro that does exactly what you want.

Some sample code to get you started:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(Earl's code may do what you want right out of the box.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Sarah wrote:
>
> Can anyone tell me if it's possible to save an Excel spreadsheet as a pipe
> delimiter instead of a tab or comma separated?

--

Dave Peterson

[Sarah]

Thanks for your reply Dave. I actually did a little more research after
posting my question and found this post from Dave Peterson back from February
and it worked great.

"What happens if you temporarily change the windows list character from a
comma
to a vertical bar?

windows start button|settings|control panel|Regional settings applet
(or regional and language options)

Click the customize button
Numbers Tab
List separator

(I'd change it back when I was done.)"

"Dave O" wrote:

> I don't think it can be done directly- but if you save it as comma
> delimited, you could open it in Word or WordPad or some other text
> editor, and do a search and replace.
>
> Or you could write some code that reads each line of the file, replaces
> commas with pipes, and rights that info to another file. Not too
> tricky.
>
>

[Sarah]

Thanks Dave. I actually found your post from February after doing more
research and it worked great. Thanks again.

"Dave Peterson" wrote:

> One way is to change that list separator.
>
> Under the Regional settings Applet under Windows control panel.
>
> In winXP, you can click on the Advanced button on the "regional options" tab.
>
> Then on the numbers tab, change the list separator character.
>
> Remember to change it back when you're done. It's a windows setting and affects
> other programs.
>
> Another option is to write a macro that does exactly what you want.
>
> Some sample code to get you started:
>
> Earl Kiosterud's Text Write program:
> www.smokeylake.com/excel
> (or directly: http://www.smokeylake.com/excel/text_write_program.htm)
>
> Chip Pearson's:
> http://www.cpearson.com/excel/imptext.htm
>
> J.E. McGimpsey's:
> http://www.mcgimpsey.com/excel/textfiles.html
>
> (Earl's code may do what you want right out of the box.)
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> Sarah wrote:
> >
> > Can anyone tell me if it's possible to save an Excel spreadsheet as a pipe
> > delimiter instead of a tab or comma separated?
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Google is nice, huh?

Sarah wrote:
>
> Thanks Dave. I actually found your post from February after doing more
> research and it worked great. Thanks again.
>
> "Dave Peterson" wrote:
>
> > One way is to change that list separator.
> >
> > Under the Regional settings Applet under Windows control panel.
> >
> > In winXP, you can click on the Advanced button on the "regional options" tab.
> >
> > Then on the numbers tab, change the list separator character.
> >
> > Remember to change it back when you're done. It's a windows setting and affects
> > other programs.
> >
> > Another option is to write a macro that does exactly what you want.
> >
> > Some sample code to get you started:
> >
> > Earl Kiosterud's Text Write program:
> > www.smokeylake.com/excel
> > (or directly: http://www.smokeylake.com/excel/text_write_program.htm)
> >
> > Chip Pearson's:
> > http://www.cpearson.com/excel/imptext.htm
> >
> > J.E. McGimpsey's:
> > http://www.mcgimpsey.com/excel/textfiles.html
> >
> > (Earl's code may do what you want right out of the box.)
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> >
> > Sarah wrote:
> > >
> > > Can anyone tell me if it's possible to save an Excel spreadsheet as a pipe
> > > delimiter instead of a tab or comma separated?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson