Constrain data format in cell

[Guest]

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!

 

[Guest]

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.