Formula for Finding ISBN Numbers
I wanted to share a Formula / Method for validating ISBN numbers since I didn't see any discussions or suggestions on how to go about, I thought I would make a post here for if anybody wanted it or had discussion ideas on it. I was recently tasked with creating a book request form where users would need to submit ISBN numbers. However we needed to validate that they are valid ISBN books. Both for ISBN 10 & 13. To so we had to have two columns: a column for ISBN #s and a column to count the number of characters in the ISBN column.
We can now move onto the formula itself: The end goal is to tell if it is a valid ID or Invalid.
We do by first making an if Statement to check the counter to tell if it's a 10 or 13. If it's a 10 it will proceed with the 10 formula. If not, then it will move onto the 13 formula.
IF([ISBN Counter]@row = 10, DO THE 10 FORMULA
The 10 formula:
IF(SUM(11 - MOD(SUM(VALUE(MID(ISBN@row, 1, 1)) * 10 + VALUE(MID(ISBN@row, 2, 1)) * 9 + VALUE(MID(ISBN@row, 3, 1)) * 8 + VALUE(MID(ISBN@row, 4, 1)) * 7 + VALUE(MID(ISBN@row, 5, 1)) * 6 + VALUE(MID(ISBN@row, 6, 1)) * 5 + VALUE(MID(ISBN@row, 7, 1)) * 4 + VALUE(MID(ISBN@row, 8, 1)) * 3 + VALUE(MID(ISBN@row, 9, 1)) * 2), 11)) = RIGHT(ISBN@row, 1), "Valid", "Invalid")
This will do a mid formula on the ISBN@row to find each value since we need to multiply each value in the ISBN by 10 in descending order. Now because this is all one string of characters we need to turn each value we harvested into a value which is why we wrap the MID function with the Value function. We add the results of these and wrap all of the those in a sum function. Which we then wrap with a Mod function. The sum being our dividend and the 11 being our divisor. We then wrap it one more time in a Sum(11- which should give us our Check digit. We then lastly wrap all of that in an if statement to see if that value is the same to the RIGHT(ISBN@row, 1). We then give it a "valid" for true and an "invalid" for false.
The 13 formula:
The 13 formula works the same way but instead of multiplying by 10 in descending order from left to right we alternate 1 and 3. We also change our mod and our sum to 10.
IF(SUM(10 - MOD(SUM(VALUE(MID(ISBN@row, 1, 1)) * 1 + VALUE(MID(ISBN@row, 2, 1)) * 3 + VALUE(MID(ISBN@row, 3, 1)) * 1 + VALUE(MID(ISBN@row, 4, 1)) * 3 + VALUE(MID(ISBN@row, 5, 1)) * 1 + VALUE(MID(ISBN@row, 6, 1)) * 3 + VALUE(MID(ISBN@row, 7, 1)) * 1 + VALUE(MID(ISBN@row, 8, 1)) * 3 + VALUE(MID(ISBN@row, 9, 1)) * 1 + VALUE(MID(ISBN@row, 10, 1)) * 3 + VALUE(MID(ISBN@row, 11, 1)) * 1 + VALUE(MID(ISBN@row, 12, 1)) * 3), 10)) = RIGHT(ISBN@row, 1), "Valid", "Invalid"))
I believe I can go a step farther create an if error if it does not meet the 10 or 13 requirements. However we made an automation to send an update request to the submitter to enter a valid ISBN number that is either 10 or 13.
Let me know what you think or if I can improve on this. Hope it helps somebody!
Whole Code:
=IF([ISBN Counter]@row = 10, IF(SUM(11 - MOD(SUM(VALUE(MID(ISBN@row, 1, 1)) * 10 + VALUE(MID(ISBN@row, 2, 1)) * 9 + VALUE(MID(ISBN@row, 3, 1)) * 8 + VALUE(MID(ISBN@row, 4, 1)) * 7 + VALUE(MID(ISBN@row, 5, 1)) * 6 + VALUE(MID(ISBN@row, 6, 1)) * 5 + VALUE(MID(ISBN@row, 7, 1)) * 4 + VALUE(MID(ISBN@row, 8, 1)) * 3 + VALUE(MID(ISBN@row, 9, 1)) * 2), 11)) = RIGHT(ISBN@row, 1), "Valid", "Invalid"), IF(SUM(10 - MOD(SUM(VALUE(MID(ISBN@row, 1, 1)) * 1 + VALUE(MID(ISBN@row, 2, 1)) * 3 + VALUE(MID(ISBN@row, 3, 1)) * 1 + VALUE(MID(ISBN@row, 4, 1)) * 3 + VALUE(MID(ISBN@row, 5, 1)) * 1 + VALUE(MID(ISBN@row, 6, 1)) * 3 + VALUE(MID(ISBN@row, 7, 1)) * 1 + VALUE(MID(ISBN@row, 8, 1)) * 3 + VALUE(MID(ISBN@row, 9, 1)) * 1 + VALUE(MID(ISBN@row, 10, 1)) * 3 + VALUE(MID(ISBN@row, 11, 1)) * 1 + VALUE(MID(ISBN@row, 12, 1)) * 3), 10)) = RIGHT(ISBN@row, 1), "Valid", "Invalid"))
Comments
-
Wow, @Walter Kerfoot thank you so much for sharing your solution! I've moved this over to "Best Practice" under Show and Tell so that other members can find it easily to add on any comments or suggestions.
Cheers,
Genevieve
