We help IT Professionals succeed at work.

How to tell Excel to specify Field delimiter (,) and Text enclosure (")

Rowby Goren
Rowby Goren asked
on
Hello,

Using Office 2007.   When I save a XLS file as a Comma delimited file, the result looks like this:

Pet Food,Alpo

I want it to look like

"Pet Food","Alpo"

I've done it before, but I don't remember how I did it.

I know this is a very basic question, but I'm stumped.

Rowby

Comment
Watch Question

VBA Developer
Commented:
Unfortunately this is not as easy as one might think.

Have a look here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/Q_22125255.html

You could also try this:
http://www.calagday.com/?p=353

One last source:
http://www.mcgimpsey.com/excel/textfiles.html

Author

Commented:
Hi broomee9,

Hmm. Surprised it isn't built into Excel by default.  I would think this is a common thing people will need.

http://www.mcgimpsey.com/excel/textfiles.html  looks like a good option.

I have to leave for the rest of the day but will try that link's suggestions and the others.

Stay tuned!

Rowby
Top Expert 2010
Commented:
Hello rowby,

In addition to those, I created a little add-in for exporting text files.  It allows you to specify the delimiter and
whather to use text qualifiers.

http://vbaexpress.com/kb/getarticle.php?kb_id=805

Regards,

Patrick

Author

Commented:
HI all,

I tried a different approach.  I opened the Excel file in Open Office Calc.   Then I saved it in Open Office Calc as a csv spreadsheet -- and it gave me the option to add the delimiters.

I am surprised that Open Office has this feature and Excel -- which has been around SO LONG -- does not have it built in.

I will split the points since you offfered a Excel solution and I will use (for example Matthewspatric's addin) in my copy of Excel.

Rowby


Author

Commented:
Thanks!

Rowby

Author

Commented:
matthewspatrick, I am trying to find the Tools menu in Office 2007.   Googling it, but in the meantime if you arrive here before I find it in Google, please let me know where the Tools menu is -- so I can add your add-in
Top Expert 2010

Commented:
rowby,

In Excel 2007, you would click the Office icon in the top left, and then select Excel Options.

You enable add-ins from there.

Patrick