If you need a way to help assure that the card numbers provided to you are
valid and weren't typo'd or corrupted during the data transmission, you can
put this code into your workbook and then add a formula in a new column to
tell you if they are at least valid CC numbers. Assuming that our card
numbers are entered into column A beginning in row 2, and that column B is
available for use, you could put a formula like this into column B:
=IF(CheckCard(A2),"Valid","Invalid Card")

As noted by others - the card number should be Text, they can even have the
dashes or spaces between groupings and the function will return proper
indicator.

Here's the code:

Function CheckCard(CCNumber As String) As Boolean
Dim CCLength As Integer
Dim Counter As Integer
Dim TmpInt As Integer
Dim TestResult As Integer

Counter = 1
'remove non-numeric characters
CCNumber = CleanUpEntry(CCNumber)
CCLength = Len(CCNumber)
Do While Counter <= CCLength
TmpInt = CInt((Mid(CCNumber, Counter, 1)))
If IsEven(CCLength) Then
'checks for 16-digit entries
If Not IsEven(Counter) Then
TmpInt = TmpInt * 2
If TmpInt 9 Then TmpInt = TmpInt - 9
End If
Else
'checks for 13 and 15 digit cards
'as Diners Club and American Express
If IsEven(Counter) Then
TmpInt = TmpInt * 2
If TmpInt 9 Then TmpInt = TmpInt - 9
End If
End If
TestResult = TestResult + TmpInt
Counter = Counter + 1
Loop
TestResult = TestResult Mod 10
CheckCard = TestResult = 0

End Function

Private Function CleanUpEntry(InputNumber As String) As String
Dim LC As Integer
Dim lsTemp As String
Dim lsChar As String

For LC = 1 To Len(InputNumber)
lsChar = Mid(InputNumber, LC, 1)
If IsNumeric(lsChar) Then lsTemp = lsTemp & lsChar
Next LC
CleanUpEntry = lsTemp
End Function

Private Function IsEven(anyNumber As Integer) As Boolean
IsEven = CBool((anyNumber Mod 2) = 0)
End Function

"Onesimus" wrote:

I work for a charity. We have an agency which collects new donors for us. On
a weekly basis, this agency sends me a spreadsheet file containing data about
the new donors which I import into our database.

One item of data they collect is credit card numbers as credit cards are
used to pay donations. Unfortunately, given that Mastercard and Visa card
numbers are 16 digits long, the credit card data we get back from the agency
always has the last value (the 16th number) as a zero. I am assuming that
Excel is accurate to 15 digits only. This means that, 9 out of 10 times, I
have to manually change the last digit in the database after it has been
imported from Excel.

Is there any way to get Excel to show a 16 digit number correctly and/or is
it possible to display a 16 digit number in 4 blocks of 4 as in 1234 1234
1234 1234 (preferably).