Credit Card Numbers
Excel supports 15 digits of precision in its cells. Credit card numbers are usually 16 digits. When you enter a number greater than 15 digits, Excel rounds it off and you get zeros padding the end of your number. Enter the number
987654321098765432
in Excel and you get this
987654321098765000
If you have a large number like this that’s causing a problem in Excel, there’s usually one of two reasons: You are an astrophysicist and you shouldn’t be using Excel to do your calculations; or the number your using is an identifying number, that is, you won’t be doing any math with it.
For identifying numbers, you should enter them as text. To enter a number as text, precede the entry with an apostrophe (’) or format the cell as Text (Format>Cells>Number) before you enter the number.
Ross:
Fair point, but i bet ther are people how do need these long numbers - might they use VBA (long/dobble?) to over come this issue - if you see what i mean?
Nice blog
6 May 2004, 5:09 amRoss:
Fair point, but i bet ther are people how do need these long numbers - might they use VBA (long/dobble?) to over come this issue - if you see what i mean?
Nice blog
6 May 2004, 5:09 amDamaris:
Formatting the cell to TEXT prior to entering number doesn’t change my problem: the number is formula continues to default to scientific number: how to change? I cannot use ” ‘ ” or ” ” “, I am using a program that is incompatible with these characters.
6 June 2005, 8:26 amDick Kusleika:
Damaris: What’s the formula?
6 June 2005, 1:56 pmed staneke:
I am intrested in this topic. I work with credit cards and I tried 0###-####-####-#### in custom, but this also returns a zero on the end. Does anyone knows how to work around this.
5 July 2005, 7:41 amJon Peltier:
You can’t have sixteen numerical digits in an Excel number. It exceeds the available precision. You have to format the credit card number as text.
6 July 2005, 8:47 pmDave:
Preceding the number with and apostrophe ‘ works great. It doesn’t show up so the number appears cleanly.
27 July 2005, 1:53 pmKC:
Excellent source of information here. Thanks!
26 August 2005, 1:58 pmaschro:
Thanks! You guys answered a question for me… but it would’ve been nice if Excel told you that *it* just lopped off the last digit… else you come back to it later and realize that it just lopped it off the last digit for a bunch of entries. Never having run up against the 15 # limit, it wasn’t something I was aware of, and I would suspect, most people aren’t.
25 May 2006, 12:32 pmblu:
Similar to
http://www.ozgrid.com/forum/showthread.php?s=32c5cdcacf371a72971a9a58eda107b4&t=11220
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
2 June 2007, 11:20 pmDim stNum As String
‘If Not Intersect(Target, Me.Range(”A1:A200″)) Is Nothing Then
If IsNumeric(Target) Then
If Len(Target) > 15 Then
Application.EnableEvents = False
stNum = Format(Target, “#### #### #### ####”)
Target = stNum
Application.EnableEvents = True
End If
End If
‘End If
End Sub
Dick Kusleika:
blu: That doesn’t work because the number is already truncated before the code runs. If you type
1234567890123456
you get
1234 5678 9012 3450
which isn’t the same.
4 June 2007, 9:21 amHeather:
ok. so it’s good to know that i’m not the only one who’s having the problem with the credit card #’s. and that excel isn’t out to get me.
but it still doesn’t help.
we have to have our credit card numbers formulated like so: ####-####-####-####
and if the number reads: ####-####-####-###0, im in mucho caliente agua. and if that’s the worst spanish you’ve ever read, im from south georgia; that’s why.
i’ve got to figure this out. and microsoft wanted $50 to tell me that excel was a piece of crap when it comes to C/C #’s.
4 September 2008, 5:47 pmAshley:
Dick, you genious
17 September 2008, 7:19 amAlan S:
Interesting, as just yesterday I was stumped by this as I was trying to import large account numbers into Access and they were being truncated. It was driving me nuts and I decided to convert the file to a .CSV and import the data as all text. Naturally, this worked and I was able to then do my joins and queries without issue.
18 September 2008, 8:01 amThanks for the help in understanding why I had to do what I did.
RevJPFunk:
The text value is an OK solution. I work with spreadsheets and DBs that use 20 digit long ID numbers; I didn’t design the things, so what can I do? The only problem I’ve encountered is that text formatted numbers are not evaluated as number values. For example, 143 > 15, however “15″ > “143″. So be warned, comparison operators will not treat these strings as number values. If you are trying to conceive of these as numeric ordered sets, it won’t work. Good luck!
10 November 2008, 11:19 am