When we enter a 16 digit number (credit card) the last digit chan.

[Guest]

How do we get the last digit to hold

 

[Frank Kabel]

Hi
either preformat the cell as 'Text' or precede the entry with an
apostrophe. e.g.
'1234567890123456

 

[Steve Jones]

Hi Frank

I was looking at the same thing yesterday by coincidence and came up with
same answer as you have given.

Is there anyway in Custom format you can Enter the sixteen numbers with a
space/hyphen between each set of four?

I've tried various options with no success.

Thanks

Steve

 

[Frank Kabel]

Hi
no there isn't. as Excel allows only 15 significant digits for a
numeric value you can't have a custom number format with 16 digits. So
this would require VBA (using an event procedure)

 

[Dave Peterson]

This works against column A. But you could change it for any range.

But that range must be formatted as text (or precede every entry with an
apostrophe).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant
Dim myStr As String

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

myStr = Right(String(16, "0") & Target.Value, 16)

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

errhandler:
Application.EnableEvents = True

End Sub

Right click on the worksheet tab that should have this behavior and select view
code. Paste this in that code window.

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

And I used dashes in this line:
Target.Value = Format(myTempVal, "0000\-0000\-0000\-0000")
but you could use:
Target.Value = Format(myTempVal, "0000 0000 0000 0000")

 

[Dave Peterson]

And a nice reference...

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