NEED EXCEL FORMULA TO CONVERT 13 DIGIT ISBN TO 10 DIGIT ISBN

[Guest]

I HAVE ALREADY FOUND A FORMULA TO CHANGE 10 DIGIT ISBN NUMBERS TO 13 DIGIT
BUT I NEED A FORMULA TO CHANGE 13 DIGIT TO 10

 

[JE McGimpsey]

One way:

A1: 978-3456789012

A2: =RIGHT(A1,10)

 

[Guest]

Afraid it's not that easy.

One way:

A1: 978-3456789012

A2: =RIGHT(A1,10)

 

[Gord Dibben]

Perhaps a sample of your data would allow greater flexibility in arriving at a
solution?


Gord Dibben MS Excel MVP

 

[Guest]

i do not know about isbn numbers - can we have an example?
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[JE McGimpsey]

Afraid it's not that easy.

That's a particularly uninformative response, especially since you gave
no formatting or other info in your post. Can you explain why not?

If you've got ISBN-13 PLUS a check digit, and one or more dashes, then
one way:

=MID(SUBSTITUTE(A1,"-",""),4,10)

And of course, after the 978 prefix is completely assigned, the 979 and
following ISBN-13's CAN'T be converted to ISBN-10, so

=IF(--LEFT(A1,3)=978,MID(SUBSTITUTE(A1,"-",""),4,10),"INVALID")

If you've got something else, try posting more info.

 

[Ragdyer]

Haven't time right now, but check this out:

http://mathworld.wolfram.com/ISBN.html

 

[Guest]

Good link - got things to working. Other link I found earlier said "reverse
the process (of getting ISBN 13)" but that wasn't giving proper results.
Using method they show does. Here's UDF to convert 978-series 13-digit ISBN
back to 10 digit ISBN (use with caution, only 2 test cases run against it so
far).

Both routines could be trimmed down and streamlined, but as usual, I am
shooting for understanding vs clever and tight here in the forum.

Don_Quixote60: you can use these in one of two ways -
#1 - put your ISBN in a cell (say ISBN13 in A1) and then in another cell put
the formula
=ISBN13toISBN10(A1)
or more directly
=ISBN13toISBN10("978-3456789012")

Both functions will accept input with or without dashes separating the
groups, just make sure it's entered as text if you need to preserve leading
zeros for the 10-digit ISBNs.

Function ISBN13toISBN10(isbn13 As String) As String
Dim tmpISBN As String
Dim LC As Integer
Dim oddTotal As Integer
Dim evenTotal As Integer
Dim cksumTotal As Integer
Dim cksumValue As Integer
Dim multiplier As Integer

If Len(isbn13) < 13 Then
MsgBox "Need 13-digit ISBN 13 value."
Exit Function
End If
For LC = 1 To Len(isbn13)
If Mid(isbn13, LC, 1) >= "0" And _
Mid(isbn13, LC, 1) <= "9" Then
tmpISBN = tmpISBN & Mid(isbn13, LC, 1)
End If
Next
If Len(tmpISBN) <> 13 Then
MsgBox "Must have a 13-digit ISBN number"
Exit Function
End If
'remove checkum digit
tmpISBN = Left(tmpISBN, Len(tmpISBN) - 1)

'remove '978' ISBN 13 digits
tmpISBN = Right(tmpISBN, Len(tmpISBN) - 3)

'calculate the original checksum value
multiplier = 10
For LC = 1 To 9
cksumTotal = cksumTotal + multiplier * _
Mid(tmpISBN, LC, 1)
multiplier = multiplier - 1
Next
cksumValue = 11 - (cksumTotal Mod 11)
If cksumValue = 10 Then
tmpISBN = tmpISBN & "X"
Else
tmpISBN = tmpISBN & Trim(Str(cksumValue))
End If
'convert to dashed format 1-3-5-1
'place ' in front to force text in cell
ISBN13toISBN10 = "'" & Left(tmpISBN, 1) & "-" & _
Mid(tmpISBN, 2, 3) & "-" & Mid(tmpISBN, 5, 5) & _
"-" & Right(tmpISBN, 1)

End Function


Here's code to go the other way: ISBN 10 to ISBN 13 (978 series). NOTE: not
set to handle 10-digit ISBN's ending i 'X'.

Function ISBN10toISBN13(isbn10 As String) As String
'not set up to translate ISBN 10 with 'X' as last
'character in the number
'
Dim tmpVal As String
Dim LC As Integer
Dim oddTotal As Long
Dim evenTotal As Long
Dim cksumTotal As Long
Dim cksumValue As Integer

If Len(isbn10) < 10 Then
MsgBox "Must have a 10-digit ISBN number"
Exit Function
End If
For LC = 1 To Len(isbn10)
If Mid(isbn10, LC, 1) >= "0" And Mid(isbn10, LC, 1) <= "9" Then
tmpVal = tmpVal & Mid(isbn10, LC, 1)
End If
Next
If Len(tmpVal) <> 10 Then
MsgBox "Must have a 10-digit ISBN number"
Exit Function
End If
tmpVal = Left("978" & tmpVal, 12)
'we'll do this in 2 steps to show it 'simply'
For LC = Len(tmpVal) To 1 Step -2 ' all odd characters
'odd numbers are multiplied by 3
oddTotal = oddTotal + (Val(Mid(tmpVal, LC, 1)) * 3)
Next
'do the even numbers
For LC = (Len(tmpVal) - 1) To 1 Step -2
evenTotal = evenTotal + Val(Mid(tmpVal, LC, 1))
Next
cksumTotal = oddTotal + evenTotal
cksumValue = 10 - (cksumTotal Mod 10)
tmpVal = tmpVal & Trim(Str(cksumValue))
'format it in 3-1-3-5-1 groupings
'place ' in front to force text in cell
ISBN10toISBN13 = "'" & Left(tmpVal, 3) & "-" & _
Mid(tmpVal, 4, 1) & "-" & Mid(tmpVal, 5, 3) & "-" & _
Mid(tmpVal, 8, 5) & "-" & Right(tmpVal, 1)
End Function

 

[bobbell]

I didn't see this question here yet so I thought I would ask if someone has the formula for converting a 10 digit ISBN to a 13 digit ISBN and vice-versa. I know there are several sites that offer conversion such as ISBN.org from Bowker and a few spreadsheets that have macros for it but someone might find the formula useful.