27 thoughts on “Credit Card Numbers”

1. 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

2. 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

3. 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.

4. Damaris: What’s the formula?

5. 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.

6. 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.

7. Preceding the number with and apostrophe ‘ works great. It doesn’t show up so the number appears cleanly.

8. Excellent source of information here. Thanks!

9. 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.

10. Similar to
    http://www.ozgrid.com/forum/showthread.php?s=32c5cdcacf371a72971a9a58eda107b4&t=11220

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim 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

11. 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.

12. 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.

13. Dick, you genious :D

14. 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.
    Thanks for the help in understanding why I had to do what I did.

15. 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!

16. I tried the apostrophe way but why do i see the apostrophe in the sheet after i loaded it? I though it would not show, just the long number.
    Thanx

17. dick you should try what blu is saying before you write something about he’s wrong because i have tried what he says and that’s the best answer for this problem that i have found it does write any number with that length and with about 15 characters more and ashley i dont think you r a genious

18. elkaba: Did you see where I typed the “If you type” and the “you get” parts? That means that I did try it and didn’t work. But I’m not above error. I make errors all the time, so I’ll try it again. Nope, it still doesn’t work. I’ll happily change my position, but I’ll need more evidence.

19. OK. This is really annoying. I am having the same issue with 16 digit numbers. The annoying part is that the actual number is there. I have a file of about 44K records, each with a unique 16 digit number as one of the fields. The number is stored in the file correctly. If I perform a mail merge using the data, the number prints correctly. If I import the file into SAS, the number imports correctly. If I try to just look at it’s value in Excel, perform any calculation on it, select it and apply formatting, etc., the trailing 0 shows up. Why on earth would they build in the functionality to store a 16 digit number, but then not let you display it or use it? If it walks like a bug and talks like a bug, it just might be a bug.

20. I don’t know Mike, but I’d guess that storing large numbers is easy and calculating with large numbers is hard.

21. Mike –

    You’re confusing numerical values and text strings. Excel can store 32,767 characters in a cell (though it can only display 1024 characters). This is way more than the 16 characters of a credit card number. Obviously the number is stored in the file as a text string, if all 16 digits are shown.

    Excel’s numerical engine can handle 15 digits of precision. As soon as you perform a mathematical operation on a text string that contains only numerical characters, that string is coerced into a numerical value, and it switches from 1024 (or 32k) characters to 15 digits.

22. IN VBA PRGRAMMING, HOW TO SEARCH 16 DIGIT CREDIT CARD NUMBER IN EXCEL COLUMNS?

23. Regarding the macro Blu suggested, an important point was missed from the original posting on Ozgrid. To avoid truncating the 16th digit before the macro even fires, the target range must first be formatted as Text. Then when the 16-digit number is entered into the cell, Excel treats it as Text and doesn’t drop the last digit. When the macro fires, it creates a custom text format #### #### #### ####.

    FWIW, the formula =TEXT(A1,”#### #### #### ####”) will return a similarly formatted number from a cell that is formatted as Text (i.e. 1234567890123456 will give you 1234 5678 9012 3456.

24. Hi Francis,

    I tried your solution but it gave me the same error as 16th digit changed to ZERO

25. Fucking Microsoft!!! These fucking morons never considered that folks would want to store their credit card numbers in a spreadsheet?!? Microsoft’s Motto: “WE MAKE UR LIFE DIFFICULT”!

26. Oops…that formula should have read

    =TEXT(A1,”0000-0000-0000-0000″)

27. Try this…

    =CONCATENATE(LEFT(C2,4),”-“,(RIGHT(LEFT(C2,8),4)),”-“,(RIGHT(LEFT(C2,12),4)),”-“,(RIGHT(C2,4)))

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.