Formatting Cells

  • Thread starter Thread starter Andy
  • Start date Start date
A

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?
 
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()
 
Back
Top