How to write a spreadsheet formula for ISBN-10 to ISBN-13 and ASIN

I wanted to convert 10 digit ISBNs to the new 13 digit format and vice versa for my "checking second-hand book prices given an ISBN" spreadsheet. It turns out that (for the moment) any ISBN-13 starting with 978 can be converted to an ISBN-10 and this will be the same as the ASIN used by Amazon, so this can be used to look up their data. The problem is that the last digit of the ISBN may change due to it being calculated using a different ISBN standard, so to convert from one to the other your spreadsheet needs to do the checksum calculation.

The following formulae are based on a 13 or 10 digit ISBN in cell A3 and cell A6 reserved for a check digit calculation. These should be in one line in a spreadsheet cell, the layout here is to help readability.

Formula to create ISBN-13 from ISBN-10

=if(
 len(A3)<11,
 concatenate(
   "978",
   mid(A3,1,9),
   mod(
     mid(A3,1,1)*3+
     mid(A3,2,1)+
     mid(A3,3,1)*3+
     mid(A3,4,1)+
     mid(A3,5,1)*3+
     mid(A3,6,1)+
     mid(A3,7,1)*3+
     mid(A3,8,1)+
     mid(A3,9,1)*3,
     10
     )
   ),
 A3
 )

Formula to create ISBN-10 from ISBN-13 (starting with 978)

=if(
len(A3)>10,
concatenate(
  mid(A3,4,9),
  if(A6=10,"X",A6)
  ),
A3
)

To calculate the check digit (A6)

=if(
len(A3)>10,
mod(
  mid(A3,4,1)+
  mid(A3,5,1)*2+
  mid(A3,6,1)*3+
  mid(A3,7,1)*4+
  mid(A3,8,1)*5+
  mid(A3,9,1)*6+
  mid(A3,10,1)*7+
  mid(A3,11,1)*8+
  mid(A3,12,1)*9,
  11
  ),
""
)