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?

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 he
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?


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?

--

Dave Peterson

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


"Dave Peterson" wrote:

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 he
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?


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?

--

Dave Peterson

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

Pablo wrote:

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

"Dave Peterson" wrote:

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 he
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?


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?

--

Dave Peterson


--

Dave Peterson

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

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 (MVP - Excel)


"Pablo" wrote in message
...
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?