Help formatting ISBN-10 and ISBN-13 data

[redhots4]

I'm a self-proclaimed bookworm. There, I've said it. And as a data junkie, I maintain a library catalog in Excel. all books have either a 10- or 13-digit ISBN number.

I need help formatting these as follows:

0399136150 should display as 0-399-13615-0
039914563X should display as 0-399-14563-X (note it ends in a letter not a number!)
9780870212857 should display as 978-08-70212-85-7

I think I have the ISBN-13 (last row) figured out using 000-00-00000-00-0 in Custom number formatting. The ISBN-10 formatting works fine except for the ISBNs ending in X.

How do I solve this? Thanks!!

 

[lrobbo314]

How about using VBA to format them.

Book2
	A
1	0-399-13615-0
2	0-399-13615-X
3	978-08-70212-85-7
Sheet1

Sub ISBN()
Dim r As Range:         Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2

For i = 1 To UBound(AR)
    If Len(AR(i, 1)) = 10 Then
        AR(i, 1) = Format(AR(i, 1), "@-@@@-@@@@@-@")
    Else
        AR(i, 1) = Format(AR(i, 1), "@@@-@@-@@@@@-@@-@")
    End If
Next i

r.Value2 = AR
End Sub