Excel to Word conversion - Tab Delimited data showing up with quotes?

I exported an excel file (attached) and although I specified tab delimted, it convereted with quote marks?  And they're not consistent.
Some cells converted with quotes, others did not.  Can someone explain the logic behind why some numbers have quotes (even though I didn't ask for them)?
test.txt
brothertruffle880Asked:
Who is Participating?
 
McOzConnect With a Mentor Commented:
Hi brother, I went back to test this and it is definitely because of the number format you have set. Excel tries to export the number in the same format as it appears in the sheet, including thousands separators and currency symbols.

I couldn't immediately find a way to have excel "ignore" the number format when exporting, but if you change the cells to the "General" format before exporting it will drop the extra commas, dollar signs and quotes, and give you just the numbers.

Hope it helps,

McOz
0
 
McOzCommented:
At a guess, the quotes are probably there to escape the commas in your number format. You'll notice the only numbers without are those less than 1,000.

Can you try removing the thousands separator before exporting?
0
 
JT92677Commented:
If a number has a comma in it, it's not really a number, it's a text field that looks numeric. For the fields with the commas, the export logic is to surround the field with Quote (") marks to include all the characters (to avoid having the commas interpreted as field separators, another common format).

I'm not sure why your exported data even has $-signs and commas (,) unless the cells themselves were entered with $-signs, which would turn them into text.

Who entered the data into the sheet, and did they by any chance enter with $ and commas?

Jeff
0
 
brothertruffle880Author Commented:
Jeff:
the numbers with the commas and dollar signs are definitely numbers.  I just checked.  I don't know if they were originally input that way but they respond to functions and formulas.
0
 
JT92677Connect With a Mentor Commented:
Not sure why Excel doesn't honor the "type" of each column. Numbers should remain numbers, etc. Putting quotes around things causes them to be interpreted as String Constants (or just strings) and when importing to another program would probably not work very well.

Maybe there is another export format you should try that is more generic, like Standard Data File (SDF) or export to a database flat file format with fixed length fields.

I'd experiment with different export options rather than to force the cells into a specific format (General for example) that changes the original spreadsheet.

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.