Fixing UPC's that Begin with a Zero

Some product UPC's have a 0 as the first digit. Unfortunately, Excel removes this 0 from your spreadsheet by default, meaning that your UPC will only display 11 digits. If any of your UPC's begin this way, here's how to make the leading 0 visible within a valid, 12-digit UPC code:

For this example, we removed a digit in the top two rows to simulate a missing zero error. We'll show you how to quickly add the zero back in to multiple cells at once.

Right click the letter in the column heading to select the whole column and click 'Insert'.

This adds a new column next to the old. Label it 'New UPC'.

Now select the cell adjacent to your first UPC. Type this in the cell =text(.

Then click the reference cell next to it. In this example the reference cell is K2. Insert a comma then a quotation mark.

Type twelve 0's into the cell followed by another quotation mark and then close the parentheses.

Press enter - Your new cell should now have a 0 as the first digit.

After that, hover your cursor over the lower right corner of your new UPC until you see a bold '+' sign. Click and drag that to the bottom of your list - this automatically changes the formatting for all the cells. Don't worry about altering UPC's that already have 12 digits, they won't be changed.

Once your whole 'New UPC' column is filled, there are just a couple steps left.

Click the column header to select the entire column. Right click the top of the column and select 'Copy'.

Right click the column header again and select 'Paste Special…'

Select 'Values' then click 'OK'

Right click your old UPC column and delete it. You'll now have one column of UPC's with 12 digits each that's ready to upload to BoxFox.

Note: This tutorial was created on a Mac; however, the process is very similar on a Windows computer.

If you have any questions, feel free to email your spreadsheet to support@boxfox.co.