conversion of book bar code to ISBN

[GrahamBookman]

Is there a formula or method that will convert the barcode printed on book
to the ISBN. - I can scan the barcode into Excel eg barcode is 9781585955565
and the ISBN is 158595556 - the last digit in the ISBN number is a checksum
digit, but I really need the ISBN.
thanks for any suggestions/advice

 

[GrahamBookman]

oops mistyped the ISBN should be 1585955566

 

[barcodewiz]

Graham,

You can use the function below. It accepts a 13 digit EAN13 number
(the barcode number) and returns an ISBN.

Public Function EAN13ToISBN(c As String) As String
If Len(c) <> 13 Or Not IsNumeric(c) Then Exit Function

Dim isbn As String
isbn = Mid(c, 4, 9)

Dim i As Integer
Dim total As Long
For i = 1 To 9
total = total + i * Int(Mid(isbn, i, 1))
Next i

Dim cd As Integer
cd = total Mod 11

If cd = 10 Then
EAN13ToISBN = isbn & "X"
Else
EAN13ToISBN = isbn & cd
End If
End Function


Alek Szymanski
http://www.barcodewiz.com

 

[Roger Govier]

Hi Graham

try
=MID(A1,4,10)

 

[GrahamBookman]

thanks so much G.

Graham,

You can use the function below. It accepts a 13 digit EAN13 number
(the barcode number) and returns an ISBN.

Public Function EAN13ToISBN(c As String) As String
If Len(c) <> 13 Or Not IsNumeric(c) Then Exit Function

Dim isbn As String
isbn = Mid(c, 4, 9)

Dim i As Integer
Dim total As Long
For i = 1 To 9
total = total + i * Int(Mid(isbn, i, 1))
Next i

Dim cd As Integer
cd = total Mod 11

If cd = 10 Then
EAN13ToISBN = isbn & "X"
Else
EAN13ToISBN = isbn & cd
End If
End Function


Alek Szymanski
http://www.barcodewiz.com