What can we help with?

Excel doesn’t often play nice with UPCs for various reasons, so this article explains how to deal with and correct these issues. Take a look at how they work!

Video on Formatting UPCs

This video shows the correct way to format UPCs for importing. It also shows you how to troubleshoot formatting issues and what to avoid0.

⚠️Please Note: These instructions are provided for your convenience only. eTailPet does not offer support or troubleshooting for third party applications. For additional help, please utilize help resources from your spreadsheet program provider.

Excel UPC Formatting

The example above is an export of a purchase order. On the left is the Excel file, and on the right is the onscreen view. If you compare the UPCs from each, you can see that they don’t match. Here’s why.

1. UPCs should be 12 or more digits.
2. Excel automatically formats any number that is greater than 11 digits as scientific notation (see the examples above with the E and the + symbol in them).
3. Excel also drops the leading zero (0) from any number, but we will need that, as in the first example UPC on the right, above.

Formatting UPCs

When you open a CSV spreadsheet in Microsoft Excel, it deletes leading zeroes and changes any digit greater than 12 to scientific notation. Here's how to reformat scientific notation and format UPCs to include leading zeroes.

Reformat Scientific Notation in Excel

If your Product UPC or System ID appears to have a Plus Sign symbol (+) in it, then Microsoft Excel has formatted it as scientific notation. This will cause issues with the import process, so these need to be reformatted.

1. Highlight the cells or column that you will reformat.
2. Right-click inside the highlighted cells and click on Format Cells.
3. Under Category, click on Number.
4. Set the Decimal Places to 0.
5. Click OK.

Format UPCs to Avoid Missing Leading Zeroes in Excel

Since UPC is usually a 12-digit number, we can use a formula to make sure they are not deleted.

1. Insert a blank column next to the UPC column to create a temporary workspace where you can add the leading zeroes.
2. In the first row of the temporary column, in the cell adjacent to the first UPC row, enter the following formula:
   =text(A2, “000000000000”)
   • Replace A2 with the cell where the first adjacent UPC is found. In the image above, for example, the formula uses E3 instead of A2. “000000000000” is 12 zeroes to represent the 12-digit UPC number.
3. Copy and past the formula into the temporary column.
4. Copy and paste the corrected UPCs from the temporary column back to the UPC column. Be sure to right-click and paste values only (See instructions in the Change Formulas to Text section below).

Open CSV File Without Scientific Notation

If you prefer to save yourself the trouble of reformatting scientific notation after you've opened your file, you can take a few extra steps before opening it. This video guides you through the process:

Related Topics

Spreadsheet Tips and Tricks

Importing Products Best Practices and Troubleshooting

Adding and Importing Products

Imports and Exports Overview

Frequently Asked Questions

Q: I have a question, but I can't find my answer here. How do I get more assistance?

A: You can ask any question at the number listed here. Contact our support team at (323) 370-0738 for the U.S. and (888) 370-2363 for Canada.