Invalid Item Barcodes: Create a Pro-forma Excel Spreadsheet that Auto Calculates the Appropriate Check Digits for UPC Item Barcodes

Answer:

If users are unable to print UPC Item Labels, the item barcode may be incorrect. This might be due to the Check Digit affixed to the barcode. This digit is used to check for accuracy and usually consists of a single-digit computed from the other digits. To generate a valid barcode using Excel spreadsheet, open a new file and add the following formula on each respective cell as described below:

A2: Insert the '12-digit invalid barcode' here

C2: =LEFT(A2)

D2: =MID(A2,2,1)

E2: =MID(A2,3,1)

F2: =MID(A2,4,1)

G2: =MID(A2,5,1)

H2: =MID(A2,6,1)

I2: =MID(A2,7,1)        

J2: =MID(A2,8,1)

K2: =MID(A2,9,1)

L2: =MID(A2,10,1)

M2: =MID(A2,11,1)

N2: T2

P2: =SUM(VALUE(C2),VALUE(E2),VALUE(G2),VALUE(I2),VALUE(K2),VALUE(M2))

Q2: =P2*3

R2: =SUM(VALUE(D2),VALUE(F2),VALUE(H2),VALUE(J2),VALUE(L2))

S2: =SUM(Q2:R2)

T2: =V2-S2

V2: =CEILING(S2,10)

X2: =CONCATENATE(C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2)

Notes:

- Column A contains the 12-digit invalid bar code which cannot be printed.

- Column X contains the new 12-digit bar code valid for printing.

- Copy paste the whole row to all the succeeding rows as needed.

- You only need to change the values in Column A then new values in Column X will be automatically calculated based on the formula.

You can now use values in Column X to update item records with the appropriate UPC barcodes via CSV Import or manual input.  Save the file for future reference.