 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Data Validation
I want to use data validation on a cell where the user MUST enter any four digits, leading zeros are permitted. Can someone explain how I do this? You don't say if 0001 and 1 are to be considered equivalent, so I'll give this a shot. Data - Validation - Whole number - Between 0 and 9999.  SignatureRegards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- >I want to use data validation on a cell where the user MUST enter any > four digits, leading zeros are permitted. > > Can someone explain how I do this? > You don't say if 0001 and 1 are to be considered equivalent, so I'll give this a shot. > Data - Validation - Whole number - Between 0 and 9999. [quoted text clipped - 7 lines] > > > Can someone explain how I do this? "The user MUST enter 4 digits." That means that 0001 and 1 are NOT equivalent. Thanks for trying to help, though. I appreciate any suggestions... Try this Custom Validation formula... =AND(LEN(A1)=4,ISNUMBER(A1)) Rick >> You don't say if 0001 and 1 are to be considered equivalent, so I'll give >> this a shot. [quoted text clipped - 16 lines] > equivalent. > Thanks for trying to help, though. I appreciate any suggestions... That didn't work for me. Excel stripped off the leading 0's before applying the validation rules. And so the =len(a1) portion failed. > Try this Custom Validation formula... > [quoted text clipped - 22 lines] > > equivalent. > > Thanks for trying to help, though. I appreciate any suggestions... SignatureDave Peterson Yeah, it doesn't work for me either.<g> The OP mentioned leading zeroes in his post, so what makes you think I would have tested for that particular condition.<bg> Rick > That didn't work for me. > [quoted text clipped - 29 lines] >> > equivalent. >> > Thanks for trying to help, though. I appreciate any suggestions... On May 6, 11:46 am, "Rick Rothstein \(MVP - VB\)" <rick.newsNO.S...@NO.SPAMverizon.net> wrote: > Yeah, it doesn't work for me either.<g> > [quoted text clipped - 40 lines] > > > Dave Peterson The new formula didn't work either, but I am not considering using Dave's suggestion. Thanks all for the help. > The new formula didn't work either, Did you remember to Format the cells as Text? > but I am not considering using > Dave's suggestion. Thanks all for the help. Did you mean to include the word "not" in your statement? The way the last sentence is worded, it sounds like you do not need any further help, but the statements that precede the last sentence makes it sound like you still do. Rick On May 6, 10:58 am, "Rick Rothstein \(MVP - VB\)" <rick.newsNO.S...@NO.SPAMverizon.net> wrote: > Try this Custom Validation formula... > [quoted text clipped - 22 lines] > > equivalent. > > Thanks for trying to help, though. I appreciate any suggestions... Thanks Rick, but that doesn't work either. Your formula accepts any 4 digit number UNLESS it begins with a zero. I want to be able to enter 0123, for example. Do you have another suggestion? Try this instead... Format the cells as Text and then use this Validation formula... =AND(ISNUMBER(VALUE(C1)),LEN(C1)=4) Have you given Dave's idea any consideration? I think that sounds like a better idea to me than forcing the user to type leading zeroes. Rick > On May 6, 10:58 am, "Rick Rothstein \(MVP - VB\)" > <rick.newsNO.S...@NO.SPAMverizon.net> wrote: [quoted text clipped - 30 lines] > I want to be able to enter 0123, for example. Do you have another > suggestion? If the value is a number, then excel will see 0001 and 1 as equal. But why impose the way the user enters the data? Just use a custom numberformat of 0000 and use that data validation of between 1 and 9999. > > You don't say if 0001 and 1 are to be considered equivalent, so I'll give this a shot. > > Data - Validation - Whole number - Between 0 and 9999. [quoted text clipped - 11 lines] > equivalent. > Thanks for trying to help, though. I appreciate any suggestions... SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.