Scanning ISBN into excel (Excel 2002)

Topic

New Reply

I have a bar code scanner. I would like to know an easy way to scan the ISBN number format into excel. Any ideas?

Reply | Quote

Replies

Googling on:
excel barcode

Found plenty of hits.

Most seem to be related to the barcode software and how that works rather than it being an excel question.

Steve

Reply | Quote

It seems that I did not make my question clear. I don’t want barcode inside the spreadsheet. I want to create a simple database of books wherein one of the field is an ISBN number (ex. 0-87120-855-5 in this format). Using the barcode scanner I would like to scan the barcode usually printed on the back of books and convert the scanned number into the ISBN format mentioned earlier. It could even be scanned in on an adjacent column and then automatically converted in the right format in the ISBN column with say a special formula, function or macro whatever works. TIA.

Reply | Quote

Excel can automatically format the number using cell formatiing.

Highlight the entry cells, select Format>Cells.
Chose the Number tab, category custom. (Bootom of the list)
Use the custom format of 0-00000-000-0.

Reply | Quote

Appreciate the quick response but it goes deeper than number formatting. At least in my situation when I use the barcode scanner with excel it will output a 13 digit number (as I understand it, the numbers are encoded using the EAN-13 bar code encoding). An EAN-13 check digit is added to the end of the bar code the ISBN part is always preceeded by the the first 3 digit ( which is always 978). To generate the ISBN format that I want is to take the 13 digits drop the first three – take the next nine digits and format it like 0-00000-000-N as for the N digit – it is taken from the 13th digit (the EAN-13 check digit) apply some formula to determine the last digit of the desired ISBN format.

Here is a link to give you more idea in case my explanation is not clear.

Appreciate the help.

Reply | Quote

It seems relatively straightforward. Do you want a (mega)formula to convert the number or a custom user-defined function?

Also could you provide a few of the EAN-13 numbers and the corresponding ISBN so we could test any formula/UDF?

Steve

Reply | Quote

I am attaching a partial database and hoping to eliminate the first column. Much like scanning directly into the ISBN column and see the formatted output outright if possible. A Function would be less complicated I suppose. In any case I appreciate the help.

Reply | Quote

How does one determine where the hyphens go? they are not consistently placed in your examples

Steve

Reply | Quote

I guess it is a bit complicated because it does not follow a consistent format. This actually created additional consideration I don’t exactly know how to answer but I got around looking at the manual of my barcode scanner and the Howto FAQ on the scanner manufacturer’s website. And there it is the answer to my problem. By just scanning a bar code on the manual one can turn on and off the code conversion from ean to isbn but without the hypenation I desired. Just for information and those who may have the same dilemma my bar code scanner is from IDTECH Econoscan Bar code scanner. Steve I really appreciate the effort and your interest in helping find the solution. Thank you again for all those you responded.

Reply | Quote

I assume you no longer need the code?

If you need the hyphenation and can provide the details on where they are placed, A user function can be created. A simple formula is not possible due to the check value. All but the check value could be obtained using a MID statement. The check value needs to do some math so either a megaformula is required or a custom function. WIth the Hyphenation a custom function is most likely the best solution.

It would not be difficult, if the “logic” for the hyphenation is given. If you find the logic and need the function post back and I can work something up for you.

Steve

Reply | Quote

Steve I’ll keep your offer in mind thanks. Once I figure out the logic in the hypenation and can’t hack it I’ll surely post back. That’s why people keep coming back to this lounge because help is always on the way.

Reply | Quote

After re-examining the link you posted for more details on this, I would speculate that there is no logic in the hyphenation.

You would have to (if desired and available) create a table for the various sections and look them up in some way…

Steve

Reply | Quote

Test this code (add it to a module in the workbook), it should work for hyphenation of the English language ISBNs.

Option Explicit
Function EAN2ISBN(sEAN As String)
    Dim iLang As String
    Dim iPub As Integer
    Dim iCheck As Integer
    Dim sDecode As String
    Dim sCheck As String
    Dim sTemp As String
    Dim i As Integer
    Dim iTotal As Integer
    sTemp = Mid(sEAN, 4, 9)
    iTotal = 0
    For i = 1 To 9
        iTotal = iTotal + _
            Val(Mid(sTemp, i, 1)) * (11 - i)
    Next
    iCheck = 11 - iTotal Mod 11
    Select Case iCheck
        Case 11
            sCheck = "0"
        Case 10
            sCheck = "X"
        Case Else
            sCheck = CStr(iCheck)
    End Select
    iLang = 1
    Select Case Left(sTemp, 3)
        Case "000" To "019"
            iPub = 2
        Case "020" To "069"
            iPub = 3
        Case "070" To "084"
            iPub = 4
        Case "085" To "089"
            iPub = 5
        Case "090" To "094"
            iPub = 6
        Case "095" To "099"
            iPub = 7
        Case Else
            Select Case Left(sTemp, 5)
                Case "15500" To "18697"
                    iPub = 5
                Case "18698" To "19989"
                    iPub = 6
                Case "19990" To "19999"
                    iPub = 7
                Case Else
                    iLang = 0
            End Select
    End Select
    If iLang = 0 Then
        EAN2ISBN = sTemp
    Else
        EAN2ISBN = Left(sTemp, iLang) & "-" & _
            Mid(sTemp, iLang + 1, iPub) & "-" & _
            Mid(sTemp, iLang + iPub + 1)
    End If
        EAN2ISBN = EAN2ISBN & "-" & sCheck
End Function

The logic was created from ISBN Frequently Asked Questions. The foreign ones will get no hyphens. It could be adapted if you get the logic for foreign hyhenations (I did not look very much for them).

To use it, if the EAN13 value is in A2, enter in a cell the formula

=ean2isbn(A2)

and will give the ISBN.

The logic is it extracts the 9 chars starting in postition #4. It loops thru them getting the value and multiplying it by the “weight” the difference of the mod 11 of from 11 is the “check value” if it is a 10 and “X” is used. If an 11 “0” is used.

After getting the check value, It marks the language as haveing 1 char and looks thru the cases for english. If none is found the ilang = 0 which is used to indicate not found so no hypens are added in the main section.

If it is found, the proper place is listed and then the hyphens are added. The check value is added at the end.

Steve

Reply | Quote

Steve your’e to much. I hope I didn’t take much of your weekend but your efforts are really appreciated. Thank you very much.

Reply | Quote

Not too much time at all once I had a few moments to work on it with the logic in hand.

Steve

Reply | Quote