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

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
>

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!A1,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 Validation>Custom
=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.
>

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!A1,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 Validation>Custom
> =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.
>>