Forum Discussion

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 t...

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