Calculating an ISBN Check Digit

Ernie has a worksheet that contains, in column A, a series of ISBN numbers. Some of these numbers are in 10-digit format and some in 13-digit. Some contain dashes at various places and some don't. Ernie knows that the last digit of an ISBN is a check digit based on a specific formula. He needs a way to calculate if that last digit is correct or not, but has no idea if this is best done with a worksheet formula or a macro.

Given the data that Ernie is working with and the differing formulas used in computing ISBN check digits, it is best to use a user-defined function to calculate whether the check digit is valid or not. The following is a good all-purpose macro that can do the trick:

Function CheckISBN(rng As Range) As Boolean
    Dim sTemp As String
    Dim sDigits As String
    Dim J As Integer
    Dim K As Integer
    Dim bXFlag As Boolean
    Dim iTtl As Integer
    Dim iMult As Integer
    Dim iChk As Integer
    Dim sChk As String

    sTemp = rng.Text

    ' Check to see if rightmost character is X
    ' This comes into play only for 10-digit ISBNs
    bXFlag = False
    If Right(sTemp, 1) = "X" Then bXFlag = True

    ' Strip everything except digits
    sDigits = ""
    For J = 1 To Len(sTemp)
        If Asc(Mid(sTemp, J, 1)) > 47 And _
           Asc(Mid(sTemp, J, 1)) < 58 Then
            sDigits = sDigits & Mid(sTemp, J, 1)
        End If
    Next J

    ' Add back in the X, if necessary
    If bXFlag Then sDigits = sDigits & "X"

    Select Case Len(sDigits)
        Case 10   ' 10-digit ISBN
            K = 0
            For J = 1 To Len(sDigits) - 1
                K = K + (Val(Mid(sDigits, J, 1)) * (11 - J))
            Next

            sChk = Trim(Str(11 - (K Mod 11)))
            If sChk = "10" Then sChk = "X"
            CheckISBN = True
            If sChk <> Right(sDigits, 1) Then CheckISBN = False
        Case 13   ' 13-digit ISBN
            K = 0
            iMult = 1
            For J = 1 To Len(sDigits) - 1
                K = K + (iMult * Val(Mid(sDigits, J, 1)))
                iMult = 4 - iMult
            Next J
            iChk = K Mod 10
            If iChk > 0 Then iChk = 10 - iChk
            sChk = Trim(Str(iChk))
            CheckISBN = True
            If sChk <> Right(sDigits, 1) Then CheckISBN = False
        Case Else
            ' ISBN isn't either 10 or 13 digits
            CheckISBN = False
    End Select
End Function

The function will handle either 10-digit or 13-digit ISBN numbers and it won't matter what other text you may have in the cell before the ISBN or whether there are dashes, spaces, or periods between the digits of the ISBN. The code strips out any non-numeric characters, with the exception of a trailing "X", which may be significant in the case of a 10-digit ISBN.

In order to use the function, all you need to do is use, as a parameter, a cell reference that contains an ISBN value:

=CheckISBN(A1)

The function will return either True or False, depending on whether the last digit of the ISBN in cell A1 is correct. (It is the last digit that comprises the check digit for an ISBN.) Because it returns True or False, the function can be used in conditional formatting rules or in more complex formulas of your devising.

If you want to know more about the actual calculation process for an ISBN check digit, you need to understand that ISBNs can be either 10 digits long (for ISBNs assigned in 2006 and earlier years) or 13 digits long (for ISBNs assigned after January 1, 2007). Plus, each scheme uses a different calculation method to determine the check digit. The following two web pages can help you understand the calculation methods:

https://isbn-information.com/the-10-digit-isbn.html
https://isbn-information.com/check-digit-for-the-13-digit-isbn.html

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13798) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.