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