Excel only keeps track of 15 significant digits for numbers. If you want more
than that, you have to enter your value as text (preformat the range as Text or
start each entry with an apostrophe '1234).
Saved from a previous post:
If you type the value as text (either pre-format the cell or text and then do
the data entry or start your entry with an apostrophe), then you can use this
technique:
Type this in A1:
'1234123412341234
and use this in that 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--but you still
need to enter the data as text!
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
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
