Thanks for everyone's input. I was able to create this spreadsheets.

Thanks,
John




Harlan Grove Wrote:
"Myrna Larson" wrote...-
In case anyone is interested in a VBA solution, here's a routine. The
input
can include digits plus dashes and spaces (which are ignored). If it-
includes-
any other characters, it returns -1 to indicate an error.-
....

Me, I prefer a more pointer-like approach, and I prefer to keep error
checking outside loops.


Function LUHN(ByVal ds As String, Optional dw As Boolean = False) As
Long
Const EVENDIGITS As String = "0516273849"
Const ODDDIGITS As String = "0123456789"

Dim k As Long, n As Long
Dim ed As String, od As String

ds = Application.WorksheetFunction.Substitute(ds, " ", "")
ds = Application.WorksheetFunction.Substitute(ds, "-", "")

If ds Like "*[!0-9]*" Then
LUHN = -1
Exit Function
End If

n = Len(ds)
LUHN = -n

If dw Then
ed = EVENDIGITS
od = ODDDIGITS
Else
ed = ODDDIGITS
od = EVENDIGITS
End If

For k = n To 2 Step -2
LUHN = LUHN + InStr(od, Mid(ds, k, 1)) + InStr(ed, Mid(ds, k - 1,
1))
Next k

If k = 1 Then LUHN = LUHN + InStr(od, Mid(ds, k, 1))

LUHN = (10 - LUHN Mod 10) Mod 10
End Function[/QUOTE


--
Union70

Me, I prefer a more pointer-like approach, and I prefer to keep error
checking outside loops.

I'd call this a translation table rather than pointers. For the digits that
are not doubled, the digit N is translated to N + 1. For digits to be doubled,
you translate N to N * 2 + 1. In both cases, you subtract 9 if the result is
9.

Interestingly enough, the calls to Substitute and the Like function are so
slow that with this particular problem, it's actually faster to do the error
checking in-line. And that's in spite of your loop executing only half as many
times.