# Conversion of ISBN 10 to ISBN 13



## MadDogVachon (Mar 4, 2015)

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


----------



## Scott Huish (Mar 4, 2015)

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


----------



## MadDogVachon (Mar 4, 2015)

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


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... 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


----------



## MadDogVachon (Mar 4, 2015)

Hi,

Here's the code of guinness:

```
#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:

```
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:

```
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:

```
#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


----------



## Scott Huish (Mar 4, 2015)

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.


----------



## MadDogVachon (Mar 5, 2015)

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.


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


----------



## MadDogVachon (Mar 5, 2015)

Hi,

A little change in my code which correct a bug...

```
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


----------



## MadDogVachon (May 18, 2015)

Hi,

Using Google Advanced Search

```
isbn pgc01 site:http://www.mrexcel.com
```
 I found the page I was looking for: http://www.mrexcel.com/forum/excel-questions/285477-mod-11-formula-valid-check-digit-3.html

MDV


----------

