Format for credit card numbers

[Schmacker]

I've tried a custom format for entering credit card numbers (four groups
of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx.

I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but
each of these causes the last digit to change to zero. So if I enter
5415779800902512 I get 5415-7798-0090-2510.

Anybody already solved this problem?

 

[Gary''s Student]

Digits are being dropped because Excel thinks you are dealing with numbers,
even with your correct formatting. Format your cells as Text before entering
any data into them. Then you can enter data in any format you wish with no
digits dropped.

 

[Schmacker]

The suggestion to format the cells as Text doesn't seem to fix the
problem. If I first format a cell as text, and then apply a custom
number format (ie: ####-####-####-####) it seems to me the cell is no
longer in text format. Anyways, I'm still getting the same result.

 

[Dave Peterson]

Number formats only work for Numbers.

But if you want numbers, then you can only enter up to 15 digits.

So you can either type what you want manually (include the hyphens)...

Or you could use Text and a helper column:

=mid(a1,1,4)&"-"&mid(a1,5,4)&"-"&mid(a1,9,4)&"-"&mid(a1,13,4)

or you could use a worksheet event that does the work for you.

If you want to try this idea, rightclick on the worksheet tab that should have
this behavior. Select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant

On Error GoTo errhandler:

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

myTempVal = CDec(Target.Value)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "0000-0000-0000-0000")

errhandler:
Application.EnableEvents = True

End Sub

I used all of column A in this line:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
but you could use:
If Intersect(Target, Me.Range("c7:g99")) Is Nothing Then Exit Sub

But make sure whatever range you use is preformatted to text. If you leave it
general, then that 16th digit is already a 0 when the code starts.

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