| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Format
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Format
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Format
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 "Difficult1" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Format
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Format
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 "John" wrote in message ... 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 "Difficult1" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Format
tried it, and it works perfect! Thank you!
"Dave Peterson" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format Cell as custom type but data doesn't display like I custom. | Excel Discussion (Misc queries) | |||
| Excel 2003. Custom format gets replaced by Special format. | New Users to Excel | |||
| ignore auto format after applying a custom format?? | Excel Discussion (Misc queries) | |||
| Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
| how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Linear Mode
