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!
