How to Calculate Barcode Check Digit with Excel Formula

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.

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.).

=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.

=(MID(C5,2,1)+ MID(C5,4,1)+ MID(C5,6,1)+ MID(C5,8,1))*3

We have the total “even” numbers.

=D5+E5

Step 2:

The number we need is the rightmost digit from the Total number we just derived.

=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.

=IF(G5=0,0,10-G5)

We have successfully calculated the check digits from the barcodes.

A simpler alternative is to combine the SUM, MOD, MID, and TEXT functions.

Steps:

=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.

Things to Remember

Don’t forget to convert the EAN13 barcode to the “Text” format before applying the barcode formula.

Download Practice Workbook

Barcode Check Digit Formula.xlsx

<< Go Back to Barcode in Excel | Learn Excel