Custom Format

[Difficult1]

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?

 

[John]

Hi
Excel limits is 15 digits unless you format your numbers in Text.
HTH
John

 

[Difficult1]

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]

Hi Select the cells you want and format it as text, this must be done before
you enter your numbers.
Also you can type a Apostrophe before your numbers like this '123456 the
apostrophe will disappear in the cell when you push enter.
HTH
John

 

[Dave Peterson]

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.

 

[John]

HI again
Sorry, disregard my last post,I forgot about the dash formatting.
in Text mode you would have to type them in yourself.
John

 

[Difficult1]

tried it, and it works perfect! Thank you!

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.