ISBN10 to ISBN13

[Xiq]

Hi all,

Here is my ISBN-converter / ISBN13-recalculator *flex*

=LEFT(IF(LEN(SUBSTITUTE(TEXT(B2,"#"),"-",""))=10,"978","")&SUBSTITUTE(TEXT(B2,"#"),"-",""),12)&SUM(((10-MOD(SUM(MID(LEFT(IF(LEN(SUBSTITUTE(TEXT(B2,"#"),"-",""))=10,"978","")&SUBSTITUTE(TEXT(B2,"#"),"-",""),12),ROW(A$1:A$12),1)*{1;3;1;3;1;3;1;3;1;3;1;3}),10))=ROW(A$1:A$10))*RIGHT(ROW(A$1:A$10),1))

Input is either ISBN10 or ISBN13, with or without the dashes.

What it basically does:

1. Converting the ISBN10 to ISBN13 by adding the "978" (if needed)
2. Then it removes the old control number (last digit), leaving the 12 digits.
3. Recalculates the new ISBN13 control number.
4. Add the 12 digits & control number together
5. ...
6. Profit!

Just for fun, if you guys have a better way... I would love to know :D

 

[Luke M]

Looks interesting Xiq. Would you mind posting a sample of different ISBN numbers so we can see the input/output? I'm not as familiar with them as you are.

 

[Xiq]

Here you go :D

ISBN13 is basically a 12 digit number + a control number.
The control number is calculated as follows:

1. Multiply the even positions of the 12 digits by 3
2. Multiply the uneven positions of the 12 digits by 1
3. Sum the results to one number
4. Divide the sum result by 10 and get the remainder
5. Subtract the remainder on 10
6. If 1-9, then thats your control number.
7. If 10, then 0 is your control number

To replace step 4 and 5, you can use:

=10-MOD(Sum_result,10)

 

[Luke M]

Hi Xiq,
I was able to reduce the formula length down from 297 to 141.
=978&LEFT(RIGHT(SUBSTITUTE(A2,"-",""),10),9)&MOD(10-MOD(SUM(38,MID(RIGHT(SUBSTITUTE(A2,"-",""),10),ROW($1:$9),1)*3^MOD(ROW($1:$9),2)),10),10)

 

[Xiq]

Nice one!

[sing]Another Formula bites the dust[/sing]

I noticed that my example gave the wrong idea that every ISBN13 should start with 978 though. So your solution works only as an ISBN10 converter to ISBN13.

I only found 4 numbers that don't start with 978:

9771012039401
9770255547605
1223344444555
2312312312312

 

[Luke M]

Ah, good to know Xiq. In that case, have to bump back up to 199.
=LEFT(IF(LEN(SUBSTITUTE(A2,"-",""))=10,978,"")&SUBSTITUTE(A2,"-",""),12)&MOD(10-MOD(SUM(MID(IF(LEN(SUBSTITUTE(A2,"-",""))=10,978,"")&SUBSTITUTE(A2,"-",""),ROW($1:$12),1)*3^MOD(ROW($2:$13),2)),10),10)

 

[Luke M]

Just realized that if we take the negative of the SUM, we can remove one of the MODs.
=LEFT(IF(LEN(SUBSTITUTE(A2,"-",""))=10,978,"")&SUBSTITUTE(A2,"-",""),12)&MOD(-SUM(MID(IF(LEN(SUBSTITUTE(A2,"-",""))=10,978,"")&SUBSTITUTE(A2,"-",""),ROW($1:$12),1)*3^MOD(ROW($2:$13),2)),10)

 

[Haseeb A]

May be this 'regular' formula,

=RIGHT("978"&SUBSTITUTE(REPLACE(A2,LEN(A2),10,MOD(10-MOD(SUM(MID(RIGHT("978"&SUBSTITUTE(A2,"-",""),13),{1;2;3;4;5;6;7;8;9;10;11;12},1)*{1;3;1;3;1;3;1;3;1;3;1;3}),10),10)),"-",""),13)

 

[Xiq]

I'm happy to see I'm going in the right direction with using the smart tricks, but I'm nowhere close as you guys!

I really love Haseeb's solution, because:

1. No need for ctrl+shift+enter --> easier for non-Excel people to work with
2. No use of references other that the target cell --> easier for non-Excel people to apply for there own use + less error sensitive when moving the formula
3. No IF functions!
4. I didn't know array's could be used this way in 'regular'-formulas!
5. It's leaving out unnecessary steps I wasn't able to figure out!

Just wondering why you are using 10 num_char in your replace function, instead of just 1. Seems to work both ways.
In any case, thanks to both of you for your lovely solutions!