You can use an event macro that actually changes the value of the cell by
inserting the extra 0's and dashes.

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code. Paste this into the code window that just opened:

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, "000-0000-0000-0000-00")

ErrHandler:
Application.EnableEvents = True

End Sub

(I looked for any change in column A. Modify that if you need to.)

Now back to excel.

Format the range that gets the values as Text (format|cells|number tab|text
category).

Type some numbers in that range and test it out.

Difficult1 wrote:

That would be a good reason that it isn't working, then. How do I do it in
text with the format that I want?

"John" wrote:

Hi
Excel limits is 15 digits unless you format your numbers in Text.
HTH
John
"Difficult1" wrote in message
...
I am trying to enter a custom format that will enable the user to enter
numbers without symbols... such as a phone number... but, the format I
need
is:

xxx-xxxx-xxxx-xxxx-xx

I have tried the custom format and it works, until I enter something other
than 0's for the last two digits. If I enter 100-2021-2448-0046-95 it
returns
100-2021-2448-0046-00

Any thoughts?



--

Dave Peterson