simple validation formula required

[Guest]

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

 

[Ken Johnson]

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?

 

[Guest]

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]

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

 

[Dave Peterson]

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.

 

[Guest]

Yes Dave,
I guess that would be another option.

thanks to all

 

[Guest]

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?

 

[Dave Peterson]

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.

 

[Guest]

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.

 

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

 

[Guest]

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.

 

[Roger Govier]

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

 

[Roger Govier]

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

 

[Guest]

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