Forum Discussion

StephenStephen's avatar
StephenStephen
Qrew Assistant Captain
8 years ago

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

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?
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    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.
    • MichaelBarrow's avatar
      MichaelBarrow
      Qrew Cadet
      8 years ago
      Yes, I've had to do a similar thing with phone numbers getting exported into Google Sheets.
    • StephenStephen's avatar
      StephenStephen
      Qrew Assistant Captain
      8 years ago
      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.