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?

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?

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

--
Rick (MVP - Excel)


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

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

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

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.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

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

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

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

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" wrote:

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

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

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

Dim CheckDigit As Long

to this...

Dim CheckDigit As String

--
Rick (MVP - Excel)


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

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

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

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" wrote:

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

Dim CheckDigit As Long

to this...

Dim CheckDigit As String

--
Rick (MVP - Excel)


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

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

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

Okay, try changing it to Variant then.

--
Rick (MVP - Excel)


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

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

Dim CheckDigit As Long

to this...

Dim CheckDigit As String

--
Rick (MVP - Excel)


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

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

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

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" wrote:

Okay, try changing it to Variant then.

--
Rick (MVP - Excel)


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

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

Dim CheckDigit As Long

to this...

Dim CheckDigit As String

--
Rick (MVP - Excel)


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

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

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

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.

--
Rick (MVP - Excel)


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

Okay, try changing it to Variant then.

--
Rick (MVP - Excel)


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

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

Dim CheckDigit As Long

to this...

Dim CheckDigit As String

--
Rick (MVP - Excel)


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

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

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

Perfect! Thanks again.

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


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

Okay, try changing it to Variant then.

--
Rick (MVP - Excel)


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

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

Dim CheckDigit As Long

to this...

Dim CheckDigit As String

--
Rick (MVP - Excel)


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

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

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

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