Conversion of ISBN 10 to ISBN 13

M

MadDogVachon

New Member
Joined
Mar 4, 2015
Messages
7
Hi,

I'm looking for ISBN converter formula. And I found that thread (http://www.mrexcel.com/forum/excel-questions/285670-isbn-13-10-conversion-macro.html). The asker received an answer which is given by pgc01. But the answer is given by a link to another old thread (http://www.mrexcel.com/board2/viewtopic.php?t=297475&postdays=0&postorder=asc&start=20). That link is dead. Does anyone can help me reach it?

The answer given by shg seems to very good, but it is in Visual Basic. I don't use Visual Basic.:(

Thanks!

MDV
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Scott Huish said:
I don't know about getting to that thread but there are formula options in that thread if you don't want to use VBA in Excel. http://www.mrexcel.com/forum/excel-questions/285670-isbn-13-10-conversion-macro.html
Click to expand...
Hi Scott Huish,

Thanks for your reply. If you speek about the answer given by R-D or the one given by Rick Rothstein, they don't work for me. Maybe I don't know how to copy and paste formulas with Office 2010...:rofl: I even tried with LibreOffice Calc without success.

I found my way with Autoit. I used the script written by guinness to make mine. Thank you anyway!

MDV
 
Upvote 0
Hi,

Here's the code of guinness:
Code: 
#include <StringConstants.au3>

; #FUNCTION# ====================================================================================================================
; Name ..........: IsISBN
; Description ...: Check if a string contains a valid ISBN number.
; Syntax ........: IsISBN($sISBN, $iType)
; Parameters ....: $sISBN               - A string value containing an ISBN number.
;                  $iType               - An integer value or either 10 or 13, depending on the ISBN type to check.
; Return values .: Success: True
;                  Failure: False and sets @error to non-zero on error:
;                   1 = String was empty or contained only whitespace.
;                   2 = Type was incorrect.
;                   3 = StringToASCIIArray() was empty.
; Author ........: guinness
; Link ..........: https://en.wikipedia.org/wiki/International_Standard_Book_Number
; Example .......: Yes
; ===============================================================================================================================
Func IsISBN($sISBN, $iType)
    If Not StringStripWS($sISBN, $STR_STRIPALL) Then Return SetError(1, 0, False)

    Local Const $iType10 = 10, $iType13 = 13
    $iType = Int($iType)
    If $iType <> $iType10 And $iType <> $iType13 Then Return SetError(2, 0, False)

    Local $aArray = StringToASCIIArray($sISBN)
    Local $iLength = UBound($aArray) - 1
    If $iLength < 0 Then Return SetError(3, 0, False)

    Local Const $iNine = 57, $iZero = 48
    Local $iCounter = 0, $iSum = 0
    Switch $iType
        Case $iType10
            If $aArray[$iLength] = 88 Or $aArray[$iLength] = 120 Then
                $iLength -= 1
                $iSum = 10
            EndIf

            $iCounter = 10
            For $i = 0 To $iLength
                If $aArray[$i] < $iZero Or $aArray[$i] > $iNine Then
                    ContinueLoop
                EndIf
                $iSum += ($aArray[$i] - $iZero) * $iCounter
                $iCounter -= 1
            Next
            Return Mod($iSum, 11) = 0 ; Divisible by 11.

        Case $iType13
            Local Const $iOne = 1, $iThree = 3
            $iCounter = $iOne
            For $i = 0 To $iLength
                If $aArray[$i] < $iZero Or $aArray[$i] > $iNine Then
                    ContinueLoop
                EndIf
                $iSum += ($aArray[$i] - $iZero) * $iCounter
                $iCounter = ($iCounter = $iOne) ? $iThree : $iOne
            Next
            Return Mod($iSum, 10) = 0 ; Divisible by 10.
    EndSwitch

    Return False
EndFunc   ;==>IsISBN

His example, which need the code above:
Code: 
Example()

Func Example()
    Local Const $iType10 = 10, $iType13 = 13

    ; 10-digit ISBN numbers.
    PrintISBN('ISBN0-9752298-0-X', $iType10, True) ; Is a 10-digit ISBN number.
    PrintISBN('ISBN0-9752298-0-4', $iType10, False) ; Is not a 10-digit ISBN number.
    PrintISBN('ISBN1-84356-028-3', $iType10, True) ; Is a 10-digit ISBN number.
    PrintISBN('ISBN0-19-852663-6', $iType10, True) ; Is a 10-digit ISBN number.
    PrintISBN('ISBN 1 86197 271 7', $iType10, True) ; Is a 10-digit ISBN number.
    PrintISBN('ISBN 4 86197 271 7', $iType10, False) ; Is not a 10-digit ISBN number.

    ConsoleWrite(@CRLF) ; Empty line.

    ; 13-digit ISBN numbers.
    PrintISBN('ISBN-13: 978-0-306-40615-7', $iType13, True) ; Is a 13-digit ISBN number.
    PrintISBN('ISBN-13: 978-1-86197-876-9', $iType13, True) ; Is a 13-digit ISBN number.
    PrintISBN('ISBN-13: 978-1-86197-876-8', $iType13, False) ; Is not a 13-digit ISBN number.
EndFunc   ;==>Example

Func PrintISBN($sISBN, $iType, $bExpected)
    ConsoleWrite($sISBN & ' => ' & IsISBN($sISBN, $iType) & ' [Expected: ' & $bExpected & ']' & @CRLF)
EndFunc   ;==>PrintISBN

Here's my code, not perfect, but working for my purpose:
Code: 
Func ISBNConverter($sISBN, $iType)
    If IsISBN($sISBN, $iType) = False Then
        Return "Wrong ISBN to convert"
    EndIf

    Local Const $iType10 = 10, $iType13 = 13
    $iType = Int($iType)
    Local $aArray = StringToASCIIArray($sISBN)
    Local $iLength = UBound($aArray) - 1
    Local Const $iNine = 57, $iEight = 56, $iSeven = 55, $iZero = 48, $iHyphen = 45, $iXBig = 88, $iXsmall = 120
    Local $iCounter = 0, $iSum = 0

    Switch $iType
        Case $iType10
            If $aArray[$iLength] = 88 Or $aArray[$iLength] = 120 Then
                $iLength -= 1
                $iSum = 10
            EndIf

            Local $sNewISBN = "978-"
            $iCounter = 10
            For $i = 0 To $iLength
                If ($aArray[$i] >= $iZero And $aArray[$i] <= $iNine) Or $aArray[$i] = $iHyphen Or $aArray[$i] = $iXBig Or $aArray[$i] = $iXsmall Then
                    $sNewISBN &= Chr($aArray[$i])
                EndIf
                If $aArray[$i] < $iZero Or $aArray[$i] > $iNine Then
                    ContinueLoop
                EndIf
                $iSum += ($aArray[$i] - $iZero) * $iCounter
                $iCounter -= 1
            Next
            $sNewISBN &= Mod($iSum, 10)
            Return $sNewISBN

        Case $iType13
            Local Const $iOne = 1, $iThree = 3
            Local $sNewISBN = ""
            Local $b978_9 = False, $b978_7 = False, $b978_8 = False, $b978_Hyphen = False
            $iCounter = $iOne
            For $i = 0 To $iLength -1
                If $b978_Hyphen = False Then
                    If $b978_9 = False Then
                        If $aArray[$i] = $iNine Then
                            $b978_9 = True
                        EndIf
                    ElseIf $b978_7 = False Then
                        If $aArray[$i] = $iSeven Then
                            $b978_7 = True
                        EndIf
                    ElseIf $b978_8 = False Then
                        If $aArray[$i] = $iEight Then
                            $b978_8 = True
                        EndIf
                    ElseIf $b978_Hyphen = False Then
                        If $aArray[$i] = $iHyphen Then
                            $b978_Hyphen = True
                        EndIf
                    EndIf
                    ContinueLoop
                EndIf
                If ($aArray[$i] >= $iZero And $aArray[$i] <= $iNine) Or $aArray[$i] = $iHyphen Then
                    $sNewISBN &= Chr($aArray[$i])
                EndIf
                If $aArray[$i] < $iZero Or $aArray[$i] > $iNine Then
                    ContinueLoop
                EndIf
                $iSum += ($aArray[$i] - $iZero) * $iCounter
                $iCounter = ($iCounter = $iOne) ? $iThree : $iOne
            Next
            If Mod($iSum, 11) < 10 Then
                $sNewISBN &= Mod($iSum, 11)
            Else
                $sNewISBN &= "X"
            EndIf
            Return $sNewISBN
    EndSwitch

    Return False
EndFunc   ;==>ISBNConverter

And an exemple, which need the function of guinness to work:
Code: 
#include <StringConstants.au3>

Example2()

Func Example2()
    Local Const $iType10 = 10, $iType13 = 13

    ; 10-digit ISBN numbers.
    ConvertISBN('ISBN0-9752298-0-X', $iType10) ; Is a 10-digit ISBN number.
    ConvertISBN('ISBN0-9752298-0-4', $iType10) ; Is not a 10-digit ISBN number.
    ConvertISBN('ISBN1-84356-028-3', $iType10) ; Is a 10-digit ISBN number.
    ConvertISBN('ISBN0-19-852663-6', $iType10) ; Is a 10-digit ISBN number.
    ConvertISBN('ISBN 1 86197 271 7', $iType10) ; Is a 10-digit ISBN number.
    ConvertISBN('ISBN 4 86197 271 7', $iType10) ; Is not a 10-digit ISBN number.

    ConsoleWrite(@CRLF) ; Empty line.

    ; 13-digit ISBN numbers.
    ConvertISBN('ISBN-13: ', $iType13) ; Is a 13-digit ISBN number.
    ConvertISBN('ISBN-13: 978-1-86197-876-9', $iType13) ; Is a 13-digit ISBN number.
    ConvertISBN('ISBN-13: 978-1-86197-876-8', $iType13) ; Is not a 13-digit ISBN number.
EndFunc   ;==>Example2

Func ConvertISBN($sISBN, $iType)
    ConsoleWrite($sISBN & ' => ' & ISBNConverter($sISBN, $iType) & @CRLF)
EndFunc   ;==>ConvertISBN

MDV
 
Last edited:
Upvote 0
I'm glad you found your answer, but since you were willing to use some sort of programming language to get your result, I'm not sure why you wouldn't use the VBA that is built into Excel.
 
Upvote 0
Scott Huish said:
I'm glad you found your answer, but since you were willing to use some sort of programming language to get your result, I'm not sure why you wouldn't use the VBA that is built into Excel.
Click to expand...
Hi Scott Huish,

I used Autoit since years. If I want to use VBA, I must learn it first... My need was converting with Excel, retrieving data with Autoit and exporting into databse with MySQL using Autoit. Or converting, retrieving data and exporting into database with MySQL all using Autoit.

Thanks!

MDV
 
Upvote 0
Hi,

A little change in my code which correct a bug...
Code: 
Func ISBNConverter($sISBN, $iType)
    If IsISBN($sISBN, $iType) = False Then
        Return "Wrong ISBN to convert"
    EndIf

    Local Const $iType10 = 10, $iType13 = 13
    $iType = Int($iType)
    Local $aArray = StringToASCIIArray($sISBN)
    Local $iLength = UBound($aArray) - 1
    Local Const $iNine = 57, $iEight = 56, $iSeven = 55, $iZero = 48, $iHyphen = 45
    Local $iCounter = 0, $iSum = 0

    Switch $iType
        Case $iType10
            Local $sNewISBN = "978-"
            $iCounter = 10
            For $i = 0 To $iLength -1
                If ($aArray[$i] >= $iZero And $aArray[$i] <= $iNine) Or $aArray[$i] = $iHyphen Then
                    $sNewISBN &= Chr($aArray[$i])
                EndIf
                If $aArray[$i] < $iZero Or $aArray[$i] > $iNine Then
                    ContinueLoop
                EndIf
                $iSum += ($aArray[$i] - $iZero) * $iCounter
                $iCounter -= 1
            Next
            $sNewISBN &= Mod($iSum, 10)
            Return $sNewISBN

        Case $iType13
            Local Const $iOne = 1, $iThree = 3
            Local $sNewISBN = ""
            Local $b978_9 = False, $b978_7 = False, $b978_8 = False, $b978_Hyphen = False
            $iCounter = $iOne
            For $i = 0 To $iLength -1
                If $b978_Hyphen = False Then
                    If $b978_9 = False Then
                        If $aArray[$i] = $iNine Then
                            $b978_9 = True
                        EndIf
                    ElseIf $b978_7 = False Then
                        If $aArray[$i] = $iSeven Then
                            $b978_7 = True
                        EndIf
                    ElseIf $b978_8 = False Then
                        If $aArray[$i] = $iEight Then
                            $b978_8 = True
                        EndIf
                    ElseIf $b978_Hyphen = False Then
                        If $aArray[$i] = $iHyphen Then
                            $b978_Hyphen = True
                        EndIf
                    EndIf
                    ContinueLoop
                EndIf
                If ($aArray[$i] >= $iZero And $aArray[$i] <= $iNine) Or $aArray[$i] = $iHyphen Then
                    $sNewISBN &= Chr($aArray[$i])
                EndIf
                If $aArray[$i] < $iZero Or $aArray[$i] > $iNine Then
                    ContinueLoop
                EndIf
                $iSum += ($aArray[$i] - $iZero) * $iCounter
                $iCounter = ($iCounter = $iOne) ? $iThree : $iOne
            Next
            If Mod($iSum, 11) < 10 Then
                $sNewISBN &= Mod($iSum, 11)
            Else
                $sNewISBN &= "X"
            EndIf
            Return $sNewISBN
    EndSwitch

    Return False
EndFunc   ;==>ISBNConverter

MDV
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Conversion of ISBN13 to ISBN10
Replies
4
Views
3K
ExcelNewbie89
E
M
  • Question Question
Automated recurring calculation. No-one at my job can help me with this.
Replies
0
Views
792
Mr Sandman
M
K
  • Question Question
VBA - Place Hyperlink in Dynamic Cell in Another Workbook - Error
Replies
0
Views
611
khabi21
K
M
  • Question Question
Extreme Complicated Name Matching (Using Function/VBA) REVISED
Replies
6
Views
848
mikeleung110
M
DocAElstein
  • Question Question
Help with CriteriaRange in VBA .AdvancedFilter Method. ( calling in the Cavalry !! )
2
Replies
17
Views
25K
hiker95
H

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top