Cell Formatting
 

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.


--
grahammal
------------------------------------------------------------------------
grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=521479

Cell Formatting
 

grahammal wrote:
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.

Can the number have leading zeros?

Cell Formatting
 

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


--
jamex
------------------------------------------------------------------------
jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243
View this thread: http://www.excelforum.com/showthread...hreadid=521479

Cell Formatting
 

grahammal wrote:
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.

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

Cell Formatting
 

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


--
grahammal
------------------------------------------------------------------------
grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=521479

Cell Formatting
 

grahammal wrote:
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

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

Cell Formatting
 

jamex wrote:
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

The requirement was for digits not alpha-numeric.

Cell Formatting
 

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

Jim

"jamex" wrote in
message ...

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


--
jamex
------------------------------------------------------------------------
jamex's Profile:
http://www.excelforum.com/member.php...o&userid=32243
View this thread: http://www.excelforum.com/showthread...hreadid=521479

Cell Formatting
 

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)

"grahammal" wrote in
message ...

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.


--
grahammal
------------------------------------------------------------------------
grahammal's Profile:
http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=521479

Cell Formatting
 

One more...

Whole Number
Between:
Minimum:
5000000
Maximum:
5999999



grahammal wrote:

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

--
grahammal
------------------------------------------------------------------------
grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=521479

--

Dave Peterson

Cell Formatting
 

Dave Peterson wrote:
One more...

Whole Number
Between:
Minimum:
5000000
Maximum:
5999999
But he isn't SURE that it always begins with 5.

Cell Formatting
 

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

Cell Formatting
 

But he is sure as far as he can tell.

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

Paul Lautman wrote:

Dave Peterson wrote:
One more...

Whole Number
Between:
Minimum:
5000000
Maximum:
5999999
But he isn't SURE that it always begins with 5.

--

Dave Peterson

Cell Formatting
 

Dave Peterson wrote:
But he is sure as far as he can tell.

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

Too true :-)