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

Posted on 2012-08-20
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)?
Question by:brothertruffle880
    LVL 9

    Expert Comment

    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?
    LVL 8

    Expert Comment

    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?


    Author Comment

    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.
    LVL 9

    Accepted Solution

    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,

    LVL 8

    Assisted Solution

    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.


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now