Format of UPC Codes in Excel - Prevent Scientific Notation
This article describes how to prevent Excel from automatically converting a UPC code to scientific notation and corrupting your product catalog.
By default, when you enter a number over 12 digits in an Excel spreadsheet, it auto-corrects the number to scientific notation for brevity. For example, "879860004073" is converted to "8.7986E+11". When Excel exports the value to a CSV or Text file, it will export what you see, not the actual 12-digit value. This can wreak havoc on the UPC codes in your product database.
To prevent this, you must format the column as Text, and take some additional steps when opening a CSV file in Excel. Here are the instructions:
- When you open the CSV or TXT, tab or comma-delimited file, use the Excel Text Import Wizard. See the following article for instructions: Open tab-delimited TXT or CSV file in Excel or Open Office Calc
- Make sure to select the column type as Text for the UPC column.
- Once the file is open, select the column and format it as Text before you save it as CSV again.
- You can verify that UPC codes have not been corrupted by opening the CSV file in Notepad or Textpad.
You an use this technique for all types of unique product identifiers, known as Global Trade Item Numbers (GTINs).
- Universal Product Code (UPC)
- 12 numeric digits
- European Article Number (EAN)
- Typically 13 numeric digits (can also be 8 or 14 numeric digits)
- Japanese Article Number (JAN)
- 8 or 13 numeric digits
- International Standard Book Number (ISBN)
- ISBN-10: 10 numeric digits (last digit may be "X")
- ISBN-13: 13 numeric digits and usually starts with 978 or 979
Data Feed Files
Lasted Updated: January 17, 2013Send Feedback