Converting excel file to text

[Jeff Granger]

I need to save a simple list in an Excel (2003) file as a text file so that
when I open the file in Notepad I get:

"Item 1"
"Item 2"
"Item 3"

etc. including the quotes. Sounds easy, but if you save as a csv or a txt
file in notepad you get:

"""Item 1"""
"""Item 2"""
"""Item 3"""

Suggestions?

Jeff

 

[Guest]

Hey Jeff,
Just remove the quotes & you should be ok!
if you have a long list then us a formular to clean up the text
Regards
IAn

 

[Jeff Granger]

Ian
You would think so, however if I remove the qotes in Excel then save as a
csv or a txt I get

List 1
List 2
List 3

In Notepad - no quotes!

Jeff

 

[Dave Peterson]

You could use a helper column filled with formulas like:

=char(34) & a1 & char(34)
or
="""" & a1 & """"

Then copy that column and paste into Notepad and save there.

or

You could use a macro and write code and have complete control over what you
write.

Here are three sites that you could steal some code from:

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 program may work right out of the box.

 

[Jeff Granger]

Dave

Thanks for that.

But could you explain why the formula ="""" & a1 & """" needs four sets of
quotes?
(I've tried it - it works, I'm just trying to understand why!).

How come three sets doesn't do the job - one pair says enter the text
between the quotes, and the third one being the text to be entered?

Jeff

 

[Dave Peterson]

=""
will return an empty string.

="""
won't work, you need to double up the quotes inside those " marks.

All that's left <vbg> is:
=""""

="now is the time for: " & """Jeff""" & " to check it out"

"""J...
" is the beginning of the text string
"" represents the quote mark.

 

[Rick Rothstein \(MVP - VB\)]

But could you explain why the formula ="""" & a1 & """" needs four sets

of quotes?
(I've tried it - it works, I'm just trying to understand why!).

To put a single quote inside a string of text, you use a doubled up quote
mark like this.

="A quote mark "" in the center"

That you already know, right? OK, now remove the text after the double quote
marks and what do you have?

="A quote mark """

A quote at the end or beginning (remove the text in front of the double
quote marks for that) needs 3 quote marks in a row. Ok, now remove the rest
of the text and what do you have?

=""""

Yep, 4 quote marks in a row. Now, extend that to your original question
using text on both sides of the A1 reference...

="some "" text" & A1 & "more "" text"

That you already knew. Now, remove all of the text...

="""" & A1 & """"

and see what you have.

Rick