# How to Calculate Barcode Check Digit with Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel sometimes we need to calculate check digits from barcodes. In order to do that, you can use formulas to get your check digit. Today in this article, I am sharing with you how to get the barcode check digit formula in Excel. Stay tuned!

## 2 Easy Methods to Calculate Barcode Check Digit with Formula in Excel

In the following, I have shared 2 simple and easy methods to calculate barcode check digits with formulas in Excel.

Suppose we have a dataset of some Bar code for their EAN13 Code. Now we are going to determine the Check Digit using formulas. ### 1. Combining LEN, NUMBERVALUE, IF, RIGHT, and MID Functions for Barcode Check Digit

With the combination of LEN, NUMBERVALUE, IF, RIGHT, and MID functions you can calculate the barcode check digit in Excel. To do so, go through the steps below-

Step 1:

• First, choose a cell (D5) and write the following formula to determine the odd numbers for the code-
`=MID(C5,1,1)+ MID(C5,3,1)+ MID(C5,5,1)+ MID(C5,7,1)`

Where,

The MID function will extract a specific text or number from a given argument. • Just hit ENTER and pull the “Fill Handle” down to fill all the cells. • Next, we will determine the even numbers. For that, apply the following formula in the chosen cell (E5)-
`=(MID(C5,2,1)+ MID(C5,4,1)+ MID(C5,6,1)+ MID(C5,8,1))*3` • Click ENTER and drag the “Fill Handle” down.
• Now, you have all the even numbers in your hand. • To reach our destination we will calculate the total numbers in the cell (F5) by applying the formula-
`=D5+E5` • Hence, press ENTER and drag down the “FillHandle”.
• In summary, we have the total number in our hands. Step 2:

• This time we will extract the right digit from the total number. To do so, apply the formula in the cell (G5)-
`=RIGHT(F5,1)` • Then click ENTER and pull the “Fill Handle” down to collect all the right-side digits from the total numbers. • Finally, we will now calculate the check digit with the IF function.
• Similarly, choose a cell (H5) and write the following formula down-
`=IF(G5=0,0,10-G5)` • Hit the ENTER button from the keyboard and then drag the “Fill Handle” down.
• In conclusion, we have successfully calculated our check digit from the barcode in Excel. Simple isn’t it? ### 2. Calculate Barcode Check Digit Formula with SUM, MOD, MID, and TEXT Functions

If you are looking for a simple solution to determine the check digit from the barcode number then in the below method I have a simple solution for this. Perform the SUM, MOD, MID, and TEXT functions to get your desired output. Check the method below-

Steps:

• To start with, choose a cell (D5) and apply the formula down-
`=MOD(-SUM(MID(TEXT(B3,"00000000000\0"),{1,3,5,7,9,11;2,4,6,8,10,12},1)*{3;1}),10)` • Gently, press ENTER and drag down the “Fill Handle” to fill all the cells in the column.
• Here, we have generated our check digit from the barcode in excel without any hesitation. ## Things to Remember

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

## Conclusion

In this article, I have tried to cover all the methods to calculate barcode check digits with formulas in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform in the comment section about your experience. The Exceldemy team is always responsive to your queries. Stay tuned and keep learning.

## Related Articles #### Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  