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

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?

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?
>
>

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

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

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
>

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

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

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
>

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.
>>

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