Written by Allen Wyatt (last updated November 7, 2020)**This tip applies to** Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

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

*Note:*

If you would like to know how to use the macros described on this page (or on any other page on the *ExcelTips* sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

**Create Custom Apps with VBA!** Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out *Mastering VBA for Office 2013* today!

When you enter information into a workbook, Excel automatically recalculates every worksheet in every open workbook on ...

Discover MoreMacros can be used for all sorts of data processing needs. One need that is fairly common is the need to copy data from ...

Discover MoreWhen working with data in a macro, there are two broad categories you can manipulate: numbers and text. Sometimes you ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

2023-02-01 09:07:03

The code only converts to 10 to X, but it doesn't do the 11 to 0 part.

The correct code just for that line is shown below:

sChk = Trim(Str(11 - (K Mod 11)))

If sChk = "10" Then sChk = "X"

If sChk = "11" Then sChk = "0" ' <<<<< This is the newly added line.

CheckISBN = True

Hope it helps.

2020-11-14 05:54:26

Willy Vanhaelen

You can indent text in Notepad or Word and of course in the Visual Basic editor but if you copy it and paste in the Comments box of this site, the indents are still there but when you submit the comment the indents are gone. This is because basic HTML ignores multiple spaces.

What I did is publish a screen shot but because this is an image, HTML shows it "as is" but you can't copy this in the VB editor as you can with non-indented text of the comments. And you are right, indented code is much easier to understand. That's why I do it sometimes but I still submit the text macro code as well so it can simply be copied in the VB editor.

2020-11-13 14:07:50

Yvan Loranger

I can copy indented text if I use Notepad [or the proper font in a std word processor like Word]

2020-11-13 06:49:48

Peter Atherton

Yvan, yes it is but you can't copy it.

2020-11-12 15:01:45

Yvan Loranger

We should as much as possible indent all our code.

Much easier to understand.

2020-11-11 04:12:37

Willy Vanhaelen

Hereafter an indented version which is much clearer:

(see Figure 1 below)

**Figure 1.**

2020-11-10 11:24:32

Willy Vanhaelen

Function CheckISBN(rng As Range) As Boolean

Dim J As Integer, K As Integer

Dim sDigits As String, S As String

Dim vChk As Variant

For J = 1 To Len(rng) ' Strip non digits

S = Mid(rng, J, 1)

If IsNumeric(S) Or S = "X" Then sDigits = sDigits & S

Next J

Select Case Len(sDigits)

Case 10 ' 10-digit ISBN

For J = 1 To 9

K = K + Mid(sDigits, J, 1) * (11 - J)

Next

vChk = 11 - (K Mod 11)

If vChk = 10 Then vChk = "X"

Case 13 ' 13-digit ISBN

For J = 1 To 12

K = K + Mid(sDigits, J, 1) * Mid("131313131313", J, 1)

Next J

vChk = Right(10 - (K Mod 10), 1)

End Select

If Len(sDigits) Then CheckISBN = CStr(vChk) = Right(sDigits, 1)

End Function

2020-11-07 15:03:26

Yvan Loranger

You might find useful the webpage ISBN [or "International Standard Book Number"] in wikipedia.org

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments