How to Calculate Barcode Check Digit with Excel Formula

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!

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

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-

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

• 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-

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

• 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-

• 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 (with Easy Steps)

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-

• 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 Make Auto Enter with Barcode Scanner in Excel

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

• Creating a Barcode Scanner Macro in Excel (with Easy Steps)
• How to Add Barcode Font in Excel (Installation and Application)
• Use Barcode Scanner for Inventory in Excel (with Easy Steps)
• How to Create Barcode Without Font in Excel (2 Smart Methods)
• Use Barcode Scanner in Excel (2 Suitable Ways)
• How to Use Code 39 Barcode Font for Excel (with Easy Steps)
• Print Barcode Labels in Excel (with 4 Easy Steps)