Excel Save As... text with special characters




When saving an Excel worksheet as tab-delimited text, cells containing
double-quote (") or comma (,) are surrounded by double-quotes in the
resulting text file. I would like to be able to induce or control the
quoting of cell values in tab-delimited text output. Specifically, I would
like the content of cells containing space ( ) to be quoted in the text

Is this possible?


Myrna Larson

You will probably have to a macro to export the file according to your specs.
Seach Google for examples.

Earl Kiosterud


Instead of Save as, try creating your text file with the Text Write Program
at www.smokeylake.com/excel. You'll have to decide what bracketing
characters (text qualifiers) you need, if any, and specify. If no tab
characters will ever be in your data, you won't need them. It depends
entirely on the expectations of the program that will be reading the file.
But the Text Write Program won't put those characters around a field
containing commas if the field delimiter is a tab character, as Excel is now
doing for you. There's a treatise on text files there that may be helpful

As for cells containing a space having quotation marks put around them,
that'd probably best be done before the file is created. Probably best done
with a mirror sheet with formulas that refer to the original sheet (cell by
cell) that add the quote marks as needed. If the data is on Sheet1, the
formula in A1 might look like:

=IF(NOT(ISERROR(SEARCH(" ",Sheet1!A1))),"""" & Sheet1!A1 & """", Sheet1!A1)

Again, I'd recommend carefully looking at the requirements of the program
that will read the file before investing time in getting the file written.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question