Skip to main content
Question

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

  • June 28, 2017
  • 0 replies
  • 354 views
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?
      This topic has been closed for replies.

      • ArchiveUser
      • Registered
      • July 29, 2017
      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.
      Terms & ConditionsAccessibility statement