Converting ISBNs: 13 to 10

[Pablo]

I am a newbie to Excel programming and have a problem I need to work out. The
publishing world has switched from 10 digit ISBNs to 13 digits by adding a
prefix 978 to an existing 9 digit string and calculating the last digit or
check digit. Our new SAP system is configured to only produce the 13 digit
now, but I still need the 10.

The formula for creating the 10 digit number is to strip off the prefix and
multiplying the next 9 digits individually using a declining weight, ie...

9781418918453 - 978 1*10, 4*9, 1*8, 8*7, 9*6...
Check Digit = (11 - Mod(Sum(1*10, 4*9, 1*8, 8*7, 9*6, 1*5, 8*4, 4*3, 5*2),11))
if Check Digit = 10 then Check Digit = "X"

I started by creating a bunch of variables using Mid() to pick out each
number in the string, but I keep getting type mismatch error. I am also
running my ISBNs in column A so I need to loop through.

Any help is greatly appreciated?

 

[Rick Rothstein]

Are you looking for a VB function (to be used as a UDF maybe) or a worksheet
formula? If a function, try this...

Function ISBN13To10(ISBN As String) As String
Dim X As Long
Dim CheckDigit As Long
ISBN13To10 = Mid(ISBN, 4, 9)
For X = 1 To 9
CheckDigit = CheckDigit + (11 - X) * Mid(ISBN13To10, X, 1)
Next
CheckDigit = 11 - (CheckDigit Mod 11)
If CheckDigit = 10 Then CheckDigit = "X"
ISBN13To10 = ISBN13To10 & CheckDigit
End Function

 

[Rick Rothstein]

I just read the ending of your original message (it was off screen and I
didn't scroll down) and I see you are after a VB solution. Use this
subroutine in combination with the function I posted earlier (just change
the 3 Const statement assignments to reflect your actual setup)...

Sub ConvertISBN13To10()
Dim X As Long
Dim LastRow As Long
Const StartRow As Long = 2
Const ISBNcolumn As String = "A"
Const SheetName As String = "Sheet7"
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, ISBNcolumn).End(xlUp).Row
For X = StartRow To LastRow
.Cells(X, ISBNcolumn).Value = ISBN13To10(.Cells(X, ISBNcolumn).Value)
Next
End With
End Sub

 

[Dave Peterson]

As a UDF:

Option Explicit
Function ISBN13To10(myStr As String) As Variant

Dim cCtr As Long
Dim CheckDigit As Long

If Len(myStr) <> 13 Then
ISBN13To10 = CVErr(xlErrRef)
Exit Function
End If

For cCtr = 1 To Len(myStr)
If IsNumeric(Mid(myStr, cCtr, 1)) Then
'ok
Else
'non numeric value
ISBN13To10 = CVErr(xlErrValue)
Exit Function
End If
Next cCtr

CheckDigit = 0
For cCtr = 4 To 12
CheckDigit = CheckDigit + (Mid(myStr, cCtr, 1) * (14 - cCtr))
Next cCtr

CheckDigit = 11 - (CheckDigit Mod 11)
If CheckDigit = 10 Then
CheckDigit = "X"
End If

ISBN13To10 = Mid(myStr, 4, 9) & CheckDigit

End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

========
Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=ISBN13To10(a1)
Where A1 contains the value to check.

ps. Do you get: 1418918458
as that 10 digit ISBN for 9781418918453?

 

[meh2030]

I am a newbie to Excel programming and have a problem I need to work out.The
publishing world has switched from 10 digit ISBNs to 13 digits by adding a
prefix 978 to an existing 9 digit string and calculating the last digit or
check digit. Our new SAP system is configured to only produce the 13 digit
now, but I still need the 10.

The formula for creating the 10 digit number is to strip off the prefix and
multiplying the next 9 digits individually using a declining weight, ie....

9781418918453        -          978    1*10, 4*9, 1*8, 8*7, 9*6...
Check Digit = (11 - Mod(Sum(1*10, 4*9, 1*8, 8*7, 9*6, 1*5, 8*4, 4*3, 5*2),11))
if Check Digit = 10 then Check Digit = "X"

I started by creating a bunch of variables using Mid() to pick out each
number in the string, but I keep getting type mismatch error. I am also
running my ISBNs in column A so I need to loop through.

Any help is greatly appreciated?

Some people post quite quickly; however, I put the following together
before I noticed any other answers. As you can see, there are quite a
few different ways to get to the same place. Enjoy, Matt.

Sub ISBN()
Dim cellLen As Integer
Dim oldISBNdigits As Integer
Dim newISBNdigits As Integer
Dim myISBN As String
Dim myRng As Range
Dim myCell As Range
Dim loopCntr As Integer
Dim evalDigit As Integer
Dim sumVal As Long
Dim chkDigit As Double

'for the fixed 10 digits
oldISBNdigits = 10
newISBNdigits = 13

Set myRng = Range("A1:A5") 'set your range however you like

For Each myCell In myRng.Cells
cellLen = Len(myCell.Value)

'Assumes only ISBN numbers are in column A, but you can
''test if otherwise
If cellLen = newISBNdigits Then
myISBN = Right(myCell.Value, oldISBNdigits)
End If

If cellLen = oldISBNdigits Then
myISBN = myCell.Value
End If

sumVal = 0

For loopCntr = 1 To Len(myISBN)
evalDigit = Mid(myISBN, loopCntr, 1)

Select Case loopCntr
Case 1
sumVal = sumVal + evalDigit * 10
Debug.Print sumVal
'....add the other numbers
Case 4
sumVal = sumVal + evalDigit * 9
'....add the other numbers
End Select

chkDigit = 11 - sumVal Mod 11
If chkDigit = 10 Then chkDigit = "X"
'some output if necessary
myCell.Offset(0, 1).Value = chkDigit
Next
End If
Next

End Sub

 

[Pablo]

Rick - Thank you very much for the help.

I took UDF and Sub and updated the Const but I am getting a Type Mismatch
error. Also does this put the 10 digit output in column B. I am not seeing
anything that indicates this.

Thanks again.

 

[Pablo]

Dave - Thanks for the help and the references. I will make sure I check them
out.

 

[Rick Rothstein]

First off, no, as written, it does not put the output in Column B... it
replaces (converts) the 13-digit value in Column A with its 10-digit
equivalent value.

Second, are all your entries composed of digits only (as your example
showed) or do you have dashes or spaces in any of your ISBN numbers?

Can you locate the line that is throwing the Type Mismatch for me so I can
try and see why this isn't working for you? (I tested the code before
posting it and it worked on my system using 13-digit entries.)

 

[Pablo]

All entries are digits like in example. In stepping through the code it seems
to be getting hung up right after the CheckDigit = "X"

If CheckDigit = 10 Then CheckDigit = "X"
ISBN13To10 = ISBN13To10 & CheckDigit

 

[Rick Rothstein]

Sorry, my fault... I Dim'med a variable incorrectly. Change this
statement...

Dim CheckDigit As Long

to this...

Dim CheckDigit As String

 

[Pablo]

I think it wants to be a Long. When I changed the Dim it threw the Type error
at the line below.

CheckDigit = CheckDigit + (11 - X) * Mid(ISBN13To10, X, 1)

 

[Rick Rothstein]

Okay, try changing it to Variant then.

 

[Pablo]

That did the trick. I saw a problem with the CheckDigit being 11 so I put
another IF statement to set it to 0.

Last question. What would it take to output the converted 10 digit value to
column B?

Thanks for all your help. I really do appreciate it.

 

[Rick Rothstein]

Try this subroutine instead the my original one...

Sub ConvertISBN13To10()
Dim X As Long
Dim LastRow As Long
Const StartRow As Long = 2
Const ISBNcolumn As String = "A"
Const ISBNoutColumn As String = "B"
Const SheetName As String = "Sheet7"
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, ISBNcolumn).End(xlUp).Row
For X = StartRow To LastRow
.Cells(X, ISBNoutColumn).Value = _
ISBN13To10(.Cells(X, ISBNcolumn).Value)
Next
End With
End Sub

All I did is add another Const statement to specify the output column
(ISBNoutColumn) and changed the assignment inside the For...Next loop to use
it.

 

[Pablo]

Perfect! Thanks again.

Try this subroutine instead the my original one...

Sub ConvertISBN13To10()
Dim X As Long
Dim LastRow As Long
Const StartRow As Long = 2
Const ISBNcolumn As String = "A"
Const ISBNoutColumn As String = "B"
Const SheetName As String = "Sheet7"
With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, ISBNcolumn).End(xlUp).Row
For X = StartRow To LastRow
.Cells(X, ISBNoutColumn).Value = _
ISBN13To10(.Cells(X, ISBNcolumn).Value)
Next
End With
End Sub

All I did is add another Const statement to specify the output column
(ISBNoutColumn) and changed the assignment inside the For...Next loop to use
it.

 

[Dave Peterson]

Just curious if you even tried using the UDF???

Dave - Thanks for the help and the references. I will make sure I check them
out.