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