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! |
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! |
All times are GMT +1. The time now is 12:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com