You can create a workbook template and save it to your XLStart folder (save it
as book.xlt).

Then put everything you want new workbooks based on that template to
inherit--custom formats, page layouts, headers/footers, etc.

Then when you click on that New Icon, the new workbook will inherit all these
settings.

And numberformats work with numbers.

You could use a worksheet event that looks for changes and changes the values in
those cells to the nice format you like.

If you want to try this, 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 myCell As Range
Dim myRng As Range
Dim myStr As String

Set myRng = Intersect(Target, Me.Range("a:a"))

If myRng Is Nothing Then Exit Sub

On Error Resume Next
Application.EnableEvents = False
For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
.NumberFormat = "00-00-00"
ElseIf Len(.Value) = 6 Then
.Value = Left(.Value, 2) & "-" & _
Mid(.Value, 3, 2) & "-" & _
Right(.Value, 2)
End If
End With
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub

Change this line to just the range that you want "formatted":
Set myRng = Intersect(Target, Me.Range("a:a"))
(I used all of column A.)

And if you typed in a number, I changed the format. If you typed in text, I
changed the value.

kdw wrote:

Is there a way to save this format type so I can use it in another workbook?
The custom format only stays in the workbook it was created in.

Also, how do I create a format for something like 12-34-5F or 12-34-56?
00-00-00 works if all digits but won't work in the first example. @@-@@-@@
just repeats everything nor does @-@-@ work.

Thanks!

--

Dave Peterson