Forum Discussion

Misqueet's avatar
Misqueet
Copper Contributor
Apr 23, 2019

Exporting UPC's into Excel

I'm exporting our inventory into Excel to make re-ordering easier (that part is simple).  My problem is there are a lot of UPC's that start with 0 (I have 15,000 items in this spreadsheet).  The 0 does not come up when I export, it automatically disappears.  The UPC's should be a total of 12 digits, but any that should have a 0 as the beginning number only end up as 11 digits.  Is there a formula to add the missing 0's without having to go line by line and manually doing it?  The UPC's are in Column A if that helps at all.

excel
Formulas and Functions

2 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Apr 23, 2019
    Why do you want to show UPCs as 12-digit Numbers? Is it because you want to calculate a check digit and add it as the 13th digit of the UPCs? Please clarify.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 23, 2019

    Misqueet ,

    Better to adjust your importing procedure not to convert texts to numbers. If not, you may correct with helper column. Assume your UPC:s are in column A starting from A1

    - select it, Ctrl+1 and format as text

    - in B1 type the formula

    =IF(LEN(A1)=11,A1,"0"&A1)

    - stay on B1, in the left up box under it with address (shall be shown B1) type B15000 or what is your last cell in the range

    - press Shift and Enter (entire range in B shall be selected)

    - Ctrl+D (formula will be populated till end of the range)

    - select column B, copy it, select column A and Paste special as values here

    - delete column B

Resources