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

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
 
C

Chip Pearson

Format the cell for Text prior to entering the value, or include an
apostrophe (') at the beginning of the number.
 
R

Ron de Bruin

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 (')
 
G

G.Adamson

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?
 
J

Jerry W. Lewis

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
 
D

Dave Peterson

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.)
 
D

Dave Peterson

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
 
Top