Formatting Cells

[Andy]

I tried to format a cell so when someone types a 16 digit
number, it will insert a "-" putting the numbers in groups
of 4 ( EX: 1234-5678-9012-3456 ). I went into Format-Cells
and selected CUSTOM and created "0000-0000-0000-0000". It
inserts the "-" as I expect but it always ignores the last
digit I typed and replaces it with a 0 (zero).
Does anyone know why and how to correct this?

 

[ray]

excel limits the numbers in a cell to 15

 

[Brenda]

Create "####-####-####-####" instead.

HTH,
Brenda

 

[Andy]

I already tried the # symbol and still won't work.

 

[J.E. McGimpsey]

XL's specifications (See Help) limit number precision to 15 decimal
digits.

To retain the digits, you'll need to enter the numbers as Text. You
can pre-format the cells as Text, or enter the numbers with a
leading apostrophe (').

To insert your hyphens, you'll need to use an event macro. One way
would be to put this in the worksheet code module (right-click on
the worksheet tab, choose View Code, paste the code in the window
that opens, then click the XL icon on the toolbar to return to XL):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const cDELIM As String = "-"
Dim sTemp As String
With Target
If Not Intersect(Range("A1"), .Cells) Is Nothing Then
sTemp = Application.Substitute(.Text, "-", "")
.Value = Left(sTemp, 4) & cDELIM & Mid(sTemp, 5, 4) & _
cDELIM & Mid(sTemp, 9, 4) & cDELIM & _
Mid(sTemp, 13, 4)
End If
End With
End Sub

Change "A1" to suit.

If this needs to work exclusively for WinXL00/02/03, use VBA's
Replace() instead of Application.Substitute()