Cardbox > Forums > Macros and Programming > "Macro for conversion of isbn 10 to 13"

## Macro for conversion of isbn 10 to 13

Has anyone written a macro that will convert a 10 digit isbn field to a new 13 digit isbn field? |
Current user: [none] |

Posted By | Post |
---|---|

## Alysoun | 21-Aug-2007 10:14 I have a formula for use with a spreadsheet for converting an 10 digit isbns to 13 digit isbns - I can't seem to convert the formula into a macro. I would like to be able update my records within Cardbox without exporting them to EXCEL for conversion. |

## Charles Welling | 21-Aug-2007 14:05 Perhaps even people with no knowledge of ISBN's could help if they had your formula. Could you give it and tell us what it does? |

## Alysoun | 22-Aug-2007 09:00 This is the formula ="978" & LEFT(A1,9) & IF(10-MOD((9*1)+(7*3)+(8*1)+(MID(A1,1,1)*3)+(MID(A1,2,1)*1)+(MID(A1,3,1)*3)+(MID(A1,4,1)*1)+(MID(A1,5,1)*3)+(MID(A1,6,1)*1)+(MID(A1,7,1)*3)+(MID(A1,8,1)*1)+(MID(A1,9,1)*3),10)=10,0,10-MOD((9*1)+(7*3)+(8*1)+(MID(A1,1,1)*3)+(MID(A1,2,1)*1)+(MID(A1,3,1)*3)+(MID(A1,4,1)*1)+(MID(A1,5,1)*3)+(MID(A1,6,1)*1)+(MID(A1,7,1)*3)+(MID(A1,8,1)*1)+(MID(A1,9,1)*3),10)) A1 is the spreadsheet cell containing the isbn 10 - it recalculates it to isbn 13 |

## Mary Doyle (DAF) | 22-Aug-2007 09:45 Alysoun, I don't have time to look at this in detail at the moment, though it is something I will obviously have to do myself. My approach I suspect will be to store the existing number, remove the last digit, add the 978 prefix. Having done that I think the easiest way would then be to create an array from this number, do the relevant sum on each element of the array to get the check digit and then join them all up. (See ISBN-13: Conversions & Calculations http://www.isbn-international.org/en/download/implementation-guidelines-04.pdf). You will have to take account of whether you currently store the ISBN with or without hyphens. As I say, I don't have time just now to try this out and if someone does come up with a macro before I come back from holidays it will obviously save me a lot of work! I will make a new field to hold the old number, as this may be quoted for years to come when someone is looking for a book. Regards, Mary |

## Charles Welling | 22-Aug-2007 11:15 Here's the macro that will do the trick. It assumes that there's a field "ISBN10" containing the old ISBN, and that there's a field "ISBN13" that will hold the ISBN13. It takes the first 9 characters of the field ISBN10, removes any hyphens, prefixes the "978" and stores this in a variable "isbn10". The only changes you have to make are: -change the field names to your own fields (or change your fields to these names). isbn10="978" & left(replace(Fields("ISBN10"),"-",""),9) isbn10_1=left(isbn10,1) isbn13=isbn10_1+isbn10_2+isbn10_3+isbn10_4+isbn10_5+isbn10_6+isbn10_7+isbn10_8+isbn10_9+isbn10_10+isbn10_11+isbn10_12 |

## Charles Welling | 22-Aug-2007 17:50 Sorry, I forgot to make the macro allow for an integer result of the division by 10. Here's the corrected macro. The line beginning with "checkdigit=" etc. may look a bit complicated but it simply totals the weighted numbers, divides them by 10 and formats the result so that it always has a fractional part of 1 digit. The rightmost (=fractional) digit is substracted from 10. If the result was an integer then this digit will be "0", which results in a check digit of "10". In that case the check digit will be changed into "0". isbn10="978" & left(replace(Fields("ISBN10"),"-",""),9) isbn10_1=left(isbn10,1) checkdigit=10-right(FormatNumber((isbn10_1+isbn10_2+isbn10_3+isbn10_4+isbn10_5+isbn10_6+isbn10_7+isbn10_8+isbn10_9+isbn10_10+isbn10_11+isbn10_12)/10,1),1) |

## Mary Doyle | 22-Aug-2007 18:54 Hello Charles, Haven't tried it yet but I'm sure your macro will work very well. That's one job I won't have to sit down and work on when I come back from holidays. Very neat. Many thanks. Mary |

## Alysoun | 23-Aug-2007 11:09 Hello Charles This is absolutely wonderful. I have tried by substituting "ISBN10" & "ISBN13" for my field names and it works! - both for single records and in batch edit. This will make updating all my existing records (over 50,000 of them!) so much easier and I am sure that it will be of great use to anyone else who needs to update existing book records held in CARDBOX. Very many thanks. Alysoun |

## Jonathan Gamboa | 12-Oct-2007 15:43 Hello Charles, I am new to the Macro and VB programming but any help you could provide would be most helpful. I would like to try to implement your code in Excel 2003 but not realy sure how to use the VB editor in Excel. What would be the steps to get this code to work. Thanks in advance, Jonathan Gamboa |

## Charles Welling | 13-Oct-2007 10:59 Jonathan, I'm not familiar with Excel except for the basic concept. Adding up some cells is as far as I go. |

## MRB | 29-Nov-2007 03:41 I am new to this macro stuff and the only macro's i do i record using the record macro function in excel. Would you guys be able to help do the reverse, i.e. create a macro in excel for converting the ISBN13 to ISBN10? Any help would be greatly appreciated. |