Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Scanning ISBN into excel (Excel 2002)

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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Scanning ISBN into excel (Excel 2002)

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

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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

  11. #11
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Scanning ISBN into excel (Excel 2002)

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

    <pre>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</pre>


    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
    <pre>=ean2isbn(A2)</pre>

    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

  14. #14
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scanning ISBN into excel (Excel 2002)

    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.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Scanning ISBN into excel (Excel 2002)

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

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •