Cell Formatting

G

grahammal

How can I format a cell so that it will only allow seven digits to be
entered.
It must not allow less or more than seven.
 
P

Paul Lautman

grahammal said:
How can I format a cell so that it will only allow seven digits to be
entered.
It must not allow less or more than seven.
Click to expand...

Can the number have leading zeros?
 
J

jamex

I think no such format is available to restrict a cell to accept only 7
alphabets, but validation can restrict it:

Data->Validation
Allow=Text length
Data=Equal to
length=7
click ok

Now just type any seven alphabets in your active cell.

Cheers...Jamex
 
P

Paul Lautman

grahammal said:
How can I format a cell so that it will only allow seven digits to be
entered.
It must not allow less or more than seven.
Click to expand...

Assuming that leading zeros are allowed/expected:

1) Select the cell
2) Press Ctrl-1
3) On the Number tab select Custom
4) In the Type box type 0000000
5) Click OK
6) Go to Data->Validation
7) On the Settings tab select Allow Custom
8) Assuming that the cell in question is F8 in the Formula box type:
=AND(ISNUMBER(F8),(LEN(TEXT(F8,"0000000"))=7))
9) Click OK
 
G

grahammal

The number will never have leading zero's.
As far as I can tell it will always begin with a 5.
Examples
5393276
5393312
5393331
5393454
 
P

Paul Lautman

grahammal said:
The number will never have leading zero's.
As far as I can tell it will always begin with a 5.
Examples
5393276
5393312
5393331
5393454
Click to expand...

In that case you can change the formula in step 8 of my other post to:
=AND(ISNUMBER(F8),(LEN(F8)=7))
and leave out steps 2 to 5 inclusive.

Enjoy!!
 
P

Paul Lautman

jamex said:
I think no such format is available to restrict a cell to accept only
7 alphabets, but validation can restrict it:

Data->Validation
Allow=Text length
Data=Equal to
length=7
click ok

Now just type any seven alphabets in your active cell.

Cheers...Jamex
Click to expand...

The requirement was for digits not alpha-numeric.
 
J

Jim May

Using the Allow: Text length
seems to not exclude numbers,
although the use of the word text
suggests so,,

Jim
 
B

Bob Phillips

Use Data Validation with a type of whole number and values of 1111111 and
9999999

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
P

Paul Lautman

Bob said:
Use Data Validation with a type of whole number and values of 1111111
and 9999999
Click to expand...
Surely it'd need to be 1000000 to 9999999 otherwise 1023456 for instance
would not be valid.
 
D

Dave Peterson

But he is sure as far as he can tell.

Which is probably the best any of us can say about anything.
 
Top