Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Constrain data format in cell
I'm not sure this is the proper terminology for what I want to do, so let me
give an example. Let's say I have a phone number field that I want to have formatted as (xxx) yyy-zzzz and I want this to be required in the field, or a message box will pop up instructing the user for correct input. Or, a zip code field to have either 5 digits or xxxxx-yyyy (5+4) digits, again prompting if the data doesn't meet the criteria. I know it can be done in Access, but I can't figure it out in Excel. Is there an easy way to do it? I don't want to write code (I DON'T write code) and I certainly can't teach others to write code when they need (want) to do something like this. Thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Constrain data format in cell
I will usually enter phone numbers as a number (ie 1234567890) and use a
custom number format of (000) 000-0000 this will make the numbers look like phone numbers (ie (123) 456-7890). You can then use data validation and choose "text length" and "equal to" and put 10 as the text length. This should be okay because phone numbers never have a 0 as the first digit (you won't be able to enter 0123456789 as a number). If you want them to enter the number with the parenthesis then format the cell as text and use a custom data validation formula of =(FIND("(",A1)=1)*(FIND(") ",A1)=5)*(FIND("-",A1)=10) This will ensure that they enter a number as (123) 456-7890 zipcodes can be done in a similar fashion. "Rusty" wrote: I'm not sure this is the proper terminology for what I want to do, so let me give an example. Let's say I have a phone number field that I want to have formatted as (xxx) yyy-zzzz and I want this to be required in the field, or a message box will pop up instructing the user for correct input. Or, a zip code field to have either 5 digits or xxxxx-yyyy (5+4) digits, again prompting if the data doesn't meet the criteria. I know it can be done in Access, but I can't figure it out in Excel. Is there an easy way to do it? I don't want to write code (I DON'T write code) and I certainly can't teach others to write code when they need (want) to do something like this. Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data in cell not corresponding with entry | New Users to Excel | |||
Cell Format Changes When Data Is Entered - Not Conditional Formatt | Excel Worksheet Functions | |||
can't format cell - have tried unlocking and unprotecting | Excel Discussion (Misc queries) | |||
Cannot Format Data in Cell | Excel Discussion (Misc queries) | |||
format cell data to display stacked data | Excel Discussion (Misc queries) |