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

Combining LEN, NUMBERVALUE, IF, RIGHT, and MID Functions for Barcode Check Digit

  • Just hit ENTER and pull the “Fill Handle” down to fill all the cells.

Combining LEN, NUMBERVALUE, IF, RIGHT, and MID Functions for Barcode Check Digit

  • 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

Combining LEN, NUMBERVALUE, IF, RIGHT, and MID Functions for Barcode Check Digit

  • 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

Combining LEN, NUMBERVALUE, IF, RIGHT, and MID Functions for Barcode Check Digit

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

Combining LEN, NUMBERVALUE, IF, RIGHT, and MID Functions for Barcode Check Digit

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

Combining LEN, NUMBERVALUE, IF, RIGHT, and MID Functions for Barcode Check Digit

  • 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-
=MOD(-SUM(MID(TEXT(B3,"00000000000\0"),{1,3,5,7,9,11;2,4,6,8,10,12},1)*{3;1}),10)

Calculate Barcode Check Digit Formula with SUM, MOD, MID, and TEXT Functions

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

Calculate Barcode Check Digit Formula with SUM, MOD, MID, and TEXT Functions

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

Wasim Akram

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

Leave a reply

ExcelDemy
Logo