User Tools

Site Tools


faq:barcode_fix_routine_format_upc_11_12_digit_check_digit_checkdigit_excel

Barcode Fix Routine in Excel

CAUTION: Garbage in = Garbage out. You need to have accurate 11 character barcodes that just need the check digit calculated. The need to be provided to you so that good barcodes can be calculated.

IMPORTANT: You (the customer) are responsible for making sure the barcodes are correct. Basically that means testing the real world scenario. You scan the barcode off of the actual product into notepad and see if it matches the barcode you are loading to System Five. Then test by making a sale in System Five (live, training, or demo data)

If we place a special formula in excel we can have excel clean up barcodes that are 11 characters in length. This particular routine adds a 0 in the front of 10 character barcodes and then calculates the check digit. This was a specific case scenario as it accounts for the most common scenario we run into which is excel removed a leading 0. If the supplier of the barcodes did not provide the correct 11 or 12 digit barcode you must get the correct barcode before continuing.

REQUIREMENT: The barcode must be in column A, and you must know how to place the formula into excel and fill down. (Note: start at row 2, and I suggest placing the formula in column B.

=IF(LEN(IF(LEN(A2)=10,0&A2,A2))=11,IF(LEN(A2)=10,0&A2,A2)&
(MOD(10-MOD(((MID(IF(LEN(A2)=10,0&A2,A2),1,1)+MID(IF(LEN(A2)=10,0&A2,A2),3,1)+
MID(IF(LEN(A2)=10,0&A2,A2),5,1)+MID(IF(LEN(A2)=10,0&A2,A2),7,1)+
MID(IF(LEN(A2)=10,0&A2,A2),9,1)+MID(IF(LEN(A2)=10,0&A2,A2),11,1))*3+
MID(IF(LEN(A2)=10,0&A2,A2),2,1)+MID(IF(LEN(A2)=10,0&A2,A2),4,1)+
MID(IF(LEN(A2)=10,0&A2,A2),6,1)+MID(IF(LEN(A2)=10,0&A2,A2),8,1)+
MID(IF(LEN(A2)=10,0&A2,A2),10,1)),10),10)),IF(LEN(A2)=10,0&A2,A2))
faq/barcode_fix_routine_format_upc_11_12_digit_check_digit_checkdigit_excel.txt · Last modified: 2012/05/31 11:51 by cliff