Discussions

Expand all | Collapse all

UPC code (12 digit text field) being converted to Scientific Notation in Excel

  • 1.  UPC code (12 digit text field) being converted to Scientific Notation in Excel

    Posted 06-28-2017 18:32
    Trying to streamline the generation of a CSV file with product data, that includes as 12-digit TEXT field with a UPC code such as, "897757002605"
    The problem is that Excel has a quirk where it sees that as a number instead of text and refused to format it as text, unless you do one of the following:

    1. Use the "import" feature and use the wizard to map that field to a TEXT instead of "General" format 
    or
    2. Open opening the CSV file in excel and seeing the Scientific Notation (8.9775E+11), you then select that column, pick "CUSTOM" format and build "#######################" as your format, it then shows that data as the full UPC code.

    Neither of these is great.

    I learned that if the CSV has this, ""=""897757002605""" (all the characters, including the quotation marks) it will actually interpret that as a text field and show correctly. 

    so I tried using a formula-text field to build that output, and got it correctly in Quickbase using " for each quotation mark (since putting a quotation mark will simply include the data in between the quotes as the data) and checked "Allow some HTML"
    And unfortunately that shows " in excel instead of the "

    Sigh.

    Any thoughts?


  • 2.  RE: UPC code (12 digit text field) being converted to Scientific Notation in Excel

    Posted 07-28-2017 20:23
    I believe - though I haven't verified it - if you do a formula text field as follows, it will work:

    "'" & [UPC]

    That would be a double quote, single quote, double quote at the beginning. In theory, it should pass something like this to the CSV:

    '897757002605

    Excel reads the single quote as a text identifier, so it shouldn't push it to scientific notation...hopefully.


  • 3.  RE: UPC code (12 digit text field) being converted to Scientific Notation in Excel

    Posted 07-28-2017 20:52
    Yes, I've had to do a similar thing with phone numbers getting exported into Google Sheets.


  • 4.  RE: UPC code (12 digit text field) being converted to Scientific Notation in Excel

    Posted 07-29-2017 03:16
    I'll try that.


  • 5.  RE: UPC code (12 digit text field) being converted to Scientific Notation in Excel

    Posted 09-20-2017 21:39
    Funny. When you open it in Excel it shows this ==> '897757002605 but if you delete the ' and then re-add it the ' disappears. :(I think once the file is CSV it's all up to excel how it's going to read it.  Frustrating that Microsoft hasn't fixed this annoying behavior.  Pushgin to Scientific notation as a default is just DUMB and could at least a a choice in the Preferences.