Home |
Search |
Today's Posts |
#1
|
|||
|
|||
simple validation formula required
can anyone pls help me with a simple validation formula I require.
I need to restrict entry into a cell for just 5 numerical digits (no alphabetic characters) i use the LEN function to make sure that the number of digits is exactly equal to five, but don't know which function to use to restrict alphabetic and other characters. please help thanks |
#2
|
|||
|
|||
simple validation formula required
archeti,
I typed =AND(ISNUMBER(A1), LEN(A1)=5) into the custum formula input box of the data validation dialog and A1 was rejected if it was not a 5 digit number. Is this what you are after? |
#3
|
|||
|
|||
simple validation formula required
hi ken,
yes indeed..i've just found out about the two converse functions "isnumber" and "istext". they work fine....except if the user enters the % sign as part of his entry, which, would obviously translate into a number! "Ken Johnson" wrote: archeti, I typed =AND(ISNUMBER(A1), LEN(A1)=5) into the custum formula input box of the data validation dialog and A1 was rejected if it was not a 5 digit number. Is this what you are after? |
#4
|
|||
|
|||
simple validation formula required
archeti,
I see what you mean, I added Right(A1,1)< "%" and had no effect. You might have to use a worksheet_change sub. Ken Johnson |
#5
|
|||
|
|||
simple validation formula required
I would think that you could use:
Data|Validation Allow whole number between 1 and 99999 But then use a custom format of "00000" to show any leading 0's. archeti wrote: can anyone pls help me with a simple validation formula I require. I need to restrict entry into a cell for just 5 numerical digits (no alphabetic characters) i use the LEN function to make sure that the number of digits is exactly equal to five, but don't know which function to use to restrict alphabetic and other characters. please help thanks -- Dave Peterson |
#6
|
|||
|
|||
simple validation formula required
Yes Dave,
I guess that would be another option. thanks to all "Dave Peterson" wrote: I would think that you could use: Data|Validation Allow whole number between 1 and 99999 But then use a custom format of "00000" to show any leading 0's. archeti wrote: can anyone pls help me with a simple validation formula I require. I need to restrict entry into a cell for just 5 numerical digits (no alphabetic characters) i use the LEN function to make sure that the number of digits is exactly equal to five, but don't know which function to use to restrict alphabetic and other characters. please help thanks -- Dave Peterson |
#7
|
|||
|
|||
simple validation formula required
This isn't particularly pretty, but I think this validation formula works
(for a value in cell A1): =AND(LEN(A1)=5,ISNUMBER((-MID(A1,1,1))*(-MID(A1,2,1))*(-MID(A1,2,1))*(-MID(A1,3,1))*(-MID(A1,4,1))*(-MID(A1,5,1)))) It checks that the length is 5 characters and that each character is a number. It allows leading zeros and all zeros (00000). Does that help? -- Regards, Ron "archeti" wrote: can anyone pls help me with a simple validation formula I require. I need to restrict entry into a cell for just 5 numerical digits (no alphabetic characters) i use the LEN function to make sure that the number of digits is exactly equal to five, but don't know which function to use to restrict alphabetic and other characters. please help thanks |
#8
|
|||
|
|||
simple validation formula required
Did you enter the value as text (leading apostrophe or preformat the cell as
text)? When the cell was formatted as general, I entered 00003 and got stopped. Ron Coderre wrote: This isn't particularly pretty, but I think this validation formula works (for a value in cell A1): =AND(LEN(A1)=5,ISNUMBER((-MID(A1,1,1))*(-MID(A1,2,1))*(-MID(A1,2,1))*(-MID(A1,3,1))*(-MID(A1,4,1))*(-MID(A1,5,1)))) It checks that the length is 5 characters and that each character is a number. It allows leading zeros and all zeros (00000). Does that help? -- Regards, Ron "archeti" wrote: can anyone pls help me with a simple validation formula I require. I need to restrict entry into a cell for just 5 numerical digits (no alphabetic characters) i use the LEN function to make sure that the number of digits is exactly equal to five, but don't know which function to use to restrict alphabetic and other characters. please help thanks -- Dave Peterson |
#9
|
|||
|
|||
simple validation formula required
Yes, I set the numeric format to TEXT....otherwise, Excel would automatically
remove any leading zeros (which I'm sure you already knew). Thanks for pointing out that TEXT formattting is required. -- Regards, Ron "Dave Peterson" wrote: Did you enter the value as text (leading apostrophe or preformat the cell as text)? When the cell was formatted as general, I entered 00003 and got stopped. Ron Coderre wrote: This isn't particularly pretty, but I think this validation formula works (for a value in cell A1): =AND(LEN(A1)=5,ISNUMBER((-MID(A1,1,1))*(-MID(A1,2,1))*(-MID(A1,2,1))*(-MID(A1,3,1))*(-MID(A1,4,1))*(-MID(A1,5,1)))) It checks that the length is 5 characters and that each character is a number. It allows leading zeros and all zeros (00000). Does that help? -- Regards, Ron "archeti" wrote: can anyone pls help me with a simple validation formula I require. I need to restrict entry into a cell for just 5 numerical digits (no alphabetic characters) i use the LEN function to make sure that the number of digits is exactly equal to five, but don't know which function to use to restrict alphabetic and other characters. please help thanks -- Dave Peterson |
#10
|
|||
|
|||
simple validation formula required
And maybe:
=AND(LEN(A1)=5,ISNUMBER(-A1)) would be sufficient. But this smaller formula can be fooled by scientific notation: '132E2 So maybe it wouldn't be ok <bg. Ron Coderre wrote: Yes, I set the numeric format to TEXT....otherwise, Excel would automatically remove any leading zeros (which I'm sure you already knew). Thanks for pointing out that TEXT formattting is required. -- Regards, Ron "Dave Peterson" wrote: Did you enter the value as text (leading apostrophe or preformat the cell as text)? When the cell was formatted as general, I entered 00003 and got stopped. Ron Coderre wrote: This isn't particularly pretty, but I think this validation formula works (for a value in cell A1): =AND(LEN(A1)=5,ISNUMBER((-MID(A1,1,1))*(-MID(A1,2,1))*(-MID(A1,2,1))*(-MID(A1,3,1))*(-MID(A1,4,1))*(-MID(A1,5,1)))) It checks that the length is 5 characters and that each character is a number. It allows leading zeros and all zeros (00000). Does that help? -- Regards, Ron "archeti" wrote: can anyone pls help me with a simple validation formula I require. I need to restrict entry into a cell for just 5 numerical digits (no alphabetic characters) i use the LEN function to make sure that the number of digits is exactly equal to five, but don't know which function to use to restrict alphabetic and other characters. please help thanks -- Dave Peterson -- Dave Peterson |
#11
|
|||
|
|||
simple validation formula required
Yeah, I tried playing with =AND(LEN(A1)=5,ISNUMBER(-A1)) and couldn't get
past: plus signs, minus signs, decimal points, commas, and fractions: +1234 -12.3 1.234 1,234 2 1/2 Consequently, I had to settle for my somewhat inelegant, but functional, formula. Hopefully, a shorter method will turn up. -- Regards, Ron "Dave Peterson" wrote: And maybe: =AND(LEN(A1)=5,ISNUMBER(-A1)) would be sufficient. But this smaller formula can be fooled by scientific notation: '132E2 So maybe it wouldn't be ok <bg. Ron Coderre wrote: Yes, I set the numeric format to TEXT....otherwise, Excel would automatically remove any leading zeros (which I'm sure you already knew). Thanks for pointing out that TEXT formattting is required. -- Regards, Ron "Dave Peterson" wrote: Did you enter the value as text (leading apostrophe or preformat the cell as text)? When the cell was formatted as general, I entered 00003 and got stopped. Ron Coderre wrote: This isn't particularly pretty, but I think this validation formula works (for a value in cell A1): =AND(LEN(A1)=5,ISNUMBER((-MID(A1,1,1))*(-MID(A1,2,1))*(-MID(A1,2,1))*(-MID(A1,3,1))*(-MID(A1,4,1))*(-MID(A1,5,1)))) It checks that the length is 5 characters and that each character is a number. It allows leading zeros and all zeros (00000). Does that help? -- Regards, Ron "archeti" wrote: can anyone pls help me with a simple validation formula I require. I need to restrict entry into a cell for just 5 numerical digits (no alphabetic characters) i use the LEN function to make sure that the number of digits is exactly equal to five, but don't know which function to use to restrict alphabetic and other characters. please help thanks -- Dave Peterson -- Dave Peterson |
#12
|
|||
|
|||
simple validation formula required
Hi
One way with a bit of a cheat. Define Names as Pos1, Pos2 .... Pos5 =AND(CODE(MID(Sheet5!A1,1,1))47,CODE(MID(Sheet5!A 1,1,1)<58)) for Pos1, change the A1,1,1 to A1,2,1 A1,3,1 etc as you define each successive name. In Data ValidationCustom =AND(LEN(A1)=5,Pos1,Pos2,Pos3,Pos4,Pos5) Regards Roger Govier Ron Coderre wrote: Yeah, I tried playing with =AND(LEN(A1)=5,ISNUMBER(-A1)) and couldn't get past: plus signs, minus signs, decimal points, commas, and fractions: +1234 -12.3 1.234 1,234 2 1/2 Consequently, I had to settle for my somewhat inelegant, but functional, formula. Hopefully, a shorter method will turn up. |
#13
|
|||
|
|||
simple validation formula required
Hi
Sorry the Sheet5 part is just because I happened to be on Sheet5 of the test workbook I was using. It has no relevance to the formulae for defining the Names. Regards Roger Govier Roger Govier wrote: Hi One way with a bit of a cheat. Define Names as Pos1, Pos2 .... Pos5 =AND(CODE(MID(Sheet5!A1,1,1))47,CODE(MID(Sheet5!A 1,1,1)<58)) for Pos1, change the A1,1,1 to A1,2,1 A1,3,1 etc as you define each successive name. In Data ValidationCustom =AND(LEN(A1)=5,Pos1,Pos2,Pos3,Pos4,Pos5) Regards Roger Govier Ron Coderre wrote: Yeah, I tried playing with =AND(LEN(A1)=5,ISNUMBER(-A1)) and couldn't get past: plus signs, minus signs, decimal points, commas, and fractions: +1234 -12.3 1.234 1,234 2 1/2 Consequently, I had to settle for my somewhat inelegant, but functional, formula. Hopefully, a shorter method will turn up. |
#14
|
|||
|
|||
simple validation formula required
It's interesting to note the way my question evolved!!!
....and Ron, never mind your formula not being pretty! thanks to all for your valuable input. cheers "Ron Coderre" wrote: This isn't particularly pretty, but I think this validation formula works (for a value in cell A1): =AND(LEN(A1)=5,ISNUMBER((-MID(A1,1,1))*(-MID(A1,2,1))*(-MID(A1,2,1))*(-MID(A1,3,1))*(-MID(A1,4,1))*(-MID(A1,5,1)))) It checks that the length is 5 characters and that each character is a number. It allows leading zeros and all zeros (00000). Does that help? -- Regards, Ron "archeti" wrote: can anyone pls help me with a simple validation formula I require. I need to restrict entry into a cell for just 5 numerical digits (no alphabetic characters) i use the LEN function to make sure that the number of digits is exactly equal to five, but don't know which function to use to restrict alphabetic and other characters. please help thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation - Scroll in the formula bar for a custom criteria | Excel Worksheet Functions | |||
help please with simple formula | New Users to Excel | |||
HELP: Data > Validation ---List ----Formula | Excel Worksheet Functions | |||
simple formula with blank cells | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |