Cell Formatting

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

[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

[Paul Lautman]

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?

[Paul Lautman]

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

[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

[Paul Lautman]

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

[Paul Lautman]

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.

[Jim May]

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

Jim

"jamex" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

"grahammal" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Dave Peterson]

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

[Paul Lautman]

Dave Peterson wrote:
> One more...
>
> Whole Number
> Between:
> Minimum:
> 5000000
> Maximum:
> 5999999
But he isn't SURE that it always begins with 5.

[Paul Lautman]

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.

[Dave Peterson]

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

[Paul Lautman]

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 :-)