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