?
Solved

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

Posted on 2012-08-20
5
Medium Priority
?
432 Views
Last Modified: 2012-08-21
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
0
Comment
Question by:brothertruffle880
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:McOz
ID: 38311977
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
 
LVL 8

Expert Comment

by:JT92677
ID: 38311988
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
 

Author Comment

by:brothertruffle880
ID: 38313524
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
 
LVL 9

Accepted Solution

by:
McOz earned 1000 total points
ID: 38313585
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
 
LVL 8

Assisted Solution

by:JT92677
JT92677 earned 1000 total points
ID: 38314549
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question