In this article, we will share 2 methods to calculate barcode check digits with Excel formulas.
We’ll determine the check digits for the dataset below of some Bar codes and their EAN13 Codes.
Method 1 – Combining LEN, NUMBERVALUE, IF, RIGHT, and MID Functions
Step 1:
First, we add up the numbers in all the odd numbered positions in the EAN code (1st number, 3rd number, 5th number etc.).
- Select a cell (D5) and enter the following formula:
=MID(C5,1,1)+ MID(C5,3,1)+ MID(C5,5,1)+ MID(C5,7,1)The MID function will extract a specific text or number from a given argument.
- Press ENTER and pull the Fill Handle down to fill the rest of the column.
We have the total “odd” numbers.
Now we add up the numbers in all the even numbered positions, and multiply the sum by 3.
- Enter the following formula in cell E5:
=(MID(C5,2,1)+ MID(C5,4,1)+ MID(C5,6,1)+ MID(C5,8,1))*3
- Press ENTER and drag the Fill Handle down.
We have the total “even” numbers.
- Now we add the odd and even totals together in cell F5:
=D5+E5
- Press ENTER and drag down the Fill Handle.
Step 2:
The number we need is the rightmost digit from the Total number we just derived.
- Enter the following formula in cell G5:
=RIGHT(F5,1)
- Press ENTER and pull the Fill Handle down to return all the rightmost digits from the Total numbers.
If the rightmost digit is greater than 0, the check digit is 10 minus that number, else it’s 0.
We’ll determine the check digit using the IF function.
- In cell H5 enter the following formula:
=IF(G5=0,0,10-G5)
- Press ENTER and drag the Fill Handle down.
We have successfully calculated the check digits from the barcodes.
Read More: How to Generate Barcode Numbers in Excel
Method 2 – Using SUM, MOD, MID, and TEXT Functions
A simpler alternative is to combine the SUM, MOD, MID, and TEXT functions.
Steps:
- In cell D5 enter the following formula:
=MOD(-SUM(MID(TEXT(B3,"00000000000\0"),{1,3,5,7,9,11;2,4,6,8,10,12},1)*{3;1}),10)
- Press ENTER and drag down the Fill Handle to fill all the cells in the column.
We have successfully generated our check digits from the barcodes.
Read More: How to Convert Numbers to Barcode in Excel
Things to Remember
- Don’t forget to convert the EAN13 barcode to the “Text” format before applying the barcode formula.
Download Practice Workbook
Related Articles
- How to Create Barcode in Excel
- How to Generate 2D Barcode in Excel
- How to Create Barcode Without Font in Excel
- How to Use EAN 13 Barcode Generator in Excel
- How to Create Barcode Using 3 of 9 Font in Excel
- How to Add Barcode Font in Excel
- How to Use Code 39 Barcode Font for Excel
- How to Generate Code 128 Barcode Font for Excel
<< Go Back to Barcode in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi. The are some mistakes.
First of all, the codes in your example are not EAN-13, are EAN-8 and they already have their check digits. So the formula doesn’t generate the check digit at all.
You need to have 7 digits for EAN-8, and 12 for EAN-13. Starting from those digits use the formula to generate the final check-digit (8th or 13th, depending on the EAN standard you need).
I didn’t test the first Method, but at the second there is a mistake. It should be {1;3} because when calculating the check-digit, you multiply the sum of the “even” digits by 3, not the “odds”. So, you should have this to work: {1,3,5,7,9,11;2,4,6,8,10,12},1)*{1;3}.
Let’s say you have a 12-digit number in A1 and want to generate the 13-digit number in B1. You need to have this formula in B1:
=A1&MOD(-SUM(MID(TEXT(A1,”00000000000\0″),{1,3,5,7,9,11;2,4,6,8,10,12},1)*{1;3}),10)
Or delete the “A1&” if you only need the check-digit in B1.
Cheers!
Hello Adrian,
Thank you for your detailed and insightful feedback! You’re absolutely right, the examples shown in the article are indeed EAN-8 codes with existing check digits, not the 12-digit base used to calculate an EAN-13 check digit.
We appreciate you pointing that out. We’ll update the examples accordingly to reflect a correct 12-digit input format for EAN-13 and 7-digit for EAN-8.
Also, your correction regarding the weighting order {1;3} is spot-on. In the standard EAN-13 calculation, the sum of digits in even positions is multiplied by 3, and the sum of digits in odd positions is used directly. The corrected array multiplication you provided using:
=MOD(-SUM(MID(TEXT(A1,”000000000000″),{1,3,5,7,9,11;2,4,6,8,10,12},1)*{1;3}),10)
is more accurate for generating the 13th digit from a 12-digit EAN-13 base number.
We’re updating the article to reflect these clarifications, and we truly appreciate your contribution to improving its accuracy!
Regards
ExcelDemy