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!
How to Calculate Barcode Check Digit with Excel Formula
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?
Read More: How to Generate Barcode Numbers in Excel
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.
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
Download this practice workbook to exercise while you are reading this article.
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. Stay tuned and keep learning.
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!
