Home

Exporting UPC's into Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-481526%22%20slang%3D%22en-US%22%3EExporting%20UPC's%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481526%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20exporting%20our%20inventory%20into%20Excel%20to%20make%20re-ordering%20easier%20(that%20part%20is%20simple).%26nbsp%3B%20My%20problem%20is%20there%20are%20a%20lot%20of%20UPC's%20that%20start%20with%200%20(I%20have%2015%2C000%20items%20in%20this%20spreadsheet).%26nbsp%3B%20The%200%20does%20not%20come%20up%20when%20I%20export%2C%20it%20automatically%20disappears.%26nbsp%3B%20The%20UPC's%20should%20be%20a%20total%20of%2012%20digits%2C%20but%20any%20that%20should%20have%20a%200%20as%20the%20beginning%20number%20only%20end%20up%20as%2011%20digits.%26nbsp%3B%20Is%20there%20a%20formula%20to%20add%20the%20missing%200's%20without%20having%20to%20go%20line%20by%20line%20and%20manually%20doing%20it%3F%26nbsp%3B%20The%20UPC's%20are%20in%20Column%20A%20if%20that%20helps%20at%20all.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-481526%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481586%22%20slang%3D%22en-US%22%3ERe%3A%20Exporting%20UPC's%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481586%22%20slang%3D%22en-US%22%3EWhy%20do%20you%20want%20to%20show%20UPCs%20as%2012-digit%20Numbers%3F%20Is%20it%20because%20you%20want%20to%20calculate%20a%20check%20digit%20and%20add%20it%20as%20the%2013th%20digit%20of%20the%20UPCs%3F%20Please%20clarify.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481544%22%20slang%3D%22en-US%22%3ERe%3A%20Exporting%20UPC's%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325872%22%20target%3D%22_blank%22%3E%40Misqueet%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EBetter%20to%20adjust%20your%20importing%20procedure%20not%20to%20convert%20texts%20to%20numbers.%20If%20not%2C%20you%20may%20correct%20with%20helper%20column.%20Assume%20your%20UPC%3As%20are%20in%20column%20A%20starting%20from%20A1%3C%2FP%3E%0A%3CP%3E-%20select%20it%2C%20Ctrl%2B1%20and%20format%20as%20text%3C%2FP%3E%0A%3CP%3E-%20in%20B1%20type%20the%20formula%3C%2FP%3E%0A%3CPRE%3E%3DIF(LEN(A1)%3D11%2CA1%2C%220%22%26amp%3BA1)%3C%2FPRE%3E%0A%3CP%3E-%20stay%20on%20B1%2C%20in%20the%20left%20up%20box%20under%20it%20with%20address%20(shall%20be%20shown%20B1)%20type%20B15000%20or%20what%20is%20your%20last%20cell%20in%20the%20range%3C%2FP%3E%0A%3CP%3E-%20press%20Shift%20and%20Enter%20(entire%20range%20in%20B%20shall%20be%20selected)%3C%2FP%3E%0A%3CP%3E-%20Ctrl%2BD%20(formula%20will%20be%20populated%20till%20end%20of%20the%20range)%3C%2FP%3E%0A%3CP%3E-%20select%20column%20B%2C%20copy%20it%2C%20select%20column%20A%20and%20Paste%20special%20as%20values%20here%3C%2FP%3E%0A%3CP%3E-%20delete%20column%20B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Misqueet
Occasional Visitor

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.

2 Replies

@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

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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies