Custom Format

D

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?
 
D

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?
 
J

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
 
D

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.
 
J

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
 
D

Difficult1

tried it, and it works perfect! Thank you!

Dave Peterson said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top