Embedded commas in Excel SaveAsText file

[Ayjay]

MS Excel 2000: Commas embedded in a text string cause entire string to appear in double quotes when saved as a text file. Other records are all OK.

eg.(please excuse Spanish)

Excel format: CAMBIO DE PUERTA BALDE, 55 YARDAS
Text Format: "CAMBIO DE PUERTA BALDE, 55 YARDAS"

 

[Dreamboat]

For a quick workaround (and because I'm not sure of the correct answer), you can open that text file in Word, hit Ctrl-h to bring up the find/replace dialog box. Put a " into the find box, don't put anything in the replace with box, and hit replace all. That'll get rid of all the quotes.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Ayjay]

Exactly what I did, but it's always nice to know WHY these things happen isn't it - hence the question.

 

[Dreamboat]

Well, you're saving as CSV, which isn't a great idea when your actual data contains commas. If you can, create a separate field for 55 YARDAS part; perhaps a field called Quantity? Depending on what program will be using the text file, you could first find/replace in Excel: find the commas and replace with a pipe or other character, like a hyphen, that won't change the ultimate meaning of the data and will still be legible and understood in your ultimate program.

techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Ayjay]

No, the comma is part of a description and I'm saving as .txt

 

[Dreamboat]

Ewww. Then I don't know why.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[vbMax]

The Cause:
When a text file is saved with a comma, that record may be
interpreted as two separate records when it is read by an
application (because of the comma). Excel places quotes
(&quot around the record to ensure that it is interpreted as
one record and not two.

The Solution:
Save your data as:
Formatted Text (Space Delimited) (*.prn)
The data will be saved with the commas and without the
quotation marks. You can then rename your file from a
.prn extension to a .txt extension.