I enter in a 16 digit number and Excel turns the last digit to "O"

  • Thread starter Thread starter G.Adamson
  • Start date Start date
G

G.Adamson

Hello,
I have credit card numbers that I have entered into and
Excel spreadsheet (ex. 4444444444444444). When I entere
this in excel changes the last digit to "0" (ex.
4444444444444440). I am thinking that some sort
of "Masking" is going on? How can I convert this back to
the original true cc #?

Thank you,
G. Adamson
 
Format the cell for Text prior to entering the value, or include an
apostrophe (') at the beginning of the number.
 
Hi G.Adamson

XL's precision is limited to 15 decimal digits

See the Excel Help for
<Excel specifications and limits>
Number precision 15 digits

You can enter the number as text by

-preformatting the cell as text
-prefixing the number with an apostrophe (')
 
Chip,
Thanks for the reply. My problem is that I already have
the list in place with 1000's of cc numbers. Is there no
way to convert it back to the correct number once excel
has done this?
 
No, once you have entered it as a number, the information beyond 15
digits that cannot be represented in IEEE double precision (used by most
general purpose numeric software) is lost.

Jerry
 
I think you can.

The last digit in (some/all) credit card numbers serves as a kind of checksum
digit. But it's not quite just adding the digits.

This site explains how the credit card numbers can be validated.
http://www.merriampark.com/anatomycc.htm

If you can validate the potential credit card number, I would think you could
guess a (any?) missing digit in the number.

I formatted the cells as Number with no punctionation (dashes/commas/etc). (I
used the .text property of what's in the cell.)

So you could try inserting an extra column and putting a formula like:

=guesscard(a1)
and drag down.


Option Explicit
Function GuessCard(rng As Range) As String

'http://www.merriampark.com/anatomycc.htm

Dim iCtr As Long
Dim myStr As String
Dim okCheckSumCtr As Long

If Len(rng.Text) <> 16 _
Or Right(rng.Text, 1) <> 0 _
Or IsNumeric(rng.Value) = False Then
GuessCard = "Not correct format"
Exit Function
End If

myStr = ""
okCheckSumCtr = 0
For iCtr = 0 To 9
If CheckCard(Left(rng.Text, 15) & iCtr) = True Then
okCheckSumCtr = okCheckSumCtr + 1
myStr = Left(rng.Text, 15) & iCtr
End If
Next iCtr

Select Case okCheckSumCtr
Case Is = 0: GuessCard = "Not Valid"
Case Is = 1: GuessCard = myStr
Case Is > 1: GuessCard = "More than one!"
End Select

End Function
Function CheckCard(TestNumber As String) As Boolean
Dim iCtr As Long
Dim charSum As Long
Dim tempSum As Integer

iCtr = 1
charSum = 0
tempSum = 0
For iCtr = 1 To Len(TestNumber)
charSum = Val(Mid(TestNumber, iCtr, 1))
If (iCtr Mod 2) = 1 Then
charSum = charSum * 2
If charSum > 9 Then
charSum = charSum - 9
End If
End If
tempSum = tempSum + charSum
Next iCtr

tempSum = tempSum Mod 10
CheckCard = CBool(tempSum = 0)

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

(ps. This guessed my credit card numbers correctly.)
 
And why loop through all the possibilities (0-9)?

Why not just use the same checksum that that algorithm would return:

Option Explicit
Function GuessCard(rng As Range) As String

'http://www.merriampark.com/anatomycc.htm

Dim iCtr As Long
Dim myStr As String
Dim okCheckSumCtr As Long
Dim myCheckSum As Long

If Len(rng.Text) <> 16 _
Or Right(rng.Text, 1) <> 0 _
Or IsNumeric(rng.Value) = False Then
GuessCard = "Not correct format"
Exit Function
End If

GuessCard = Left(rng.Text, 15) & CheckCard(rng.Text)

End Function
Function CheckCard(TestNumber As String) As Long
Dim iCtr As Long
Dim charSum As Long
Dim tempSum As Integer

iCtr = 1
charSum = 0
tempSum = 0
For iCtr = 1 To Len(TestNumber)
charSum = Val(Mid(TestNumber, iCtr, 1))
If (iCtr Mod 2) = 1 Then
charSum = charSum * 2
If charSum > 9 Then
charSum = charSum - 9
End If
End If
tempSum = tempSum + charSum
Next iCtr

CheckCard = tempSum Mod 10

End Function
 
Back
Top