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
End If

If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub
End if

If IsNumeric(Target.Value) = False Then
Exit Sub
End If

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 he
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.)

Neil wrote:

Niek:

I don't know of a way to format a text field to insert dashes automatically
does one exist?
--
Neil

"Neil" wrote:

When I try to format a 16 digit number into 1111-2222-3333-4444 the last
number is displayed as a "0". How do I get the last number to display
correctly?
--
Neil

--

Dave Peterson