On Feb 6, 10:01*am, Pablo wrote:
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