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

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
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?
Photo of Stephen

Stephen

  • 760 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
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.
Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
Yes, I've had to do a similar thing with phone numbers getting exported into Google Sheets.
Photo of Stephen

Stephen

  • 760 Points 500 badge 2x thumb
I'll try that.
Photo of Stephen

Stephen

  • 760 Points 500 badge 2x thumb
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.