Format for credit card numbers

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?

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.
--
Gary''s Student


"Schmacker" wrote:

>
> 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?
>
>
> --
> Schmacker
> ------------------------------------------------------------------------
> Schmacker's Profile: http://www.excelforum.com/member.php...o&userid=28041
> View this thread: http://www.excelforum.com/showthread...hreadid=475488
>
>

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.

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

Schmacker wrote:
>
> 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.
>
> --
> Schmacker
> ------------------------------------------------------------------------
> Schmacker's Profile: http://www.excelforum.com/member.php...o&userid=28041
> View this thread: http://www.excelforum.com/showthread...hreadid=475488

--

Dave Peterson