set the number of characters in a cell

[CF]

How can I format a cell so that ONLY ten numbers can be entered; no less than
ten, and no more than ten can be entered. Also, some of these ten digit
numbers begin with a zero and I would like that zero to display as well as
count as one of the ten digits.

Thanks in advance for your help!

[Dave Peterson]

Do you want to just show the leading 0's or does the cell actually have to
contain the leading 0's?

If you just want to show them, then you can select the range
format|cells|number tab|custom category
0000000000

Then use data|validation to make sure the entered value is a whole number
between 0(?) and 9999999999.



CF wrote:
>
> How can I format a cell so that ONLY ten numbers can be entered; no less than
> ten, and no more than ten can be entered. Also, some of these ten digit
> numbers begin with a zero and I would like that zero to display as well as
> count as one of the ten digits.
>
> Thanks in advance for your help!

--

Dave Peterson

[pinmaster]

Here's one way.

Select your cells then go to Data/Validation, next select "custom" from the
"Allow" menu and in the formula box type:

=LEN(A1)=10
with A1 being the first cell in your selection and the cells formatted as text

HTH
Jean-Guy

"CF" wrote:

> How can I format a cell so that ONLY ten numbers can be entered; no less than
> ten, and no more than ten can be entered. Also, some of these ten digit
> numbers begin with a zero and I would like that zero to display as well as
> count as one of the ten digits.
>
> Thanks in advance for your help!
>

[Sloth]

Format the cells as text first
The select the Data menu and choose Validation
Under Allow choose Text length
Under Data choose Equal to
Under Length type 10
Click OK

by numbers I assume you mean digits. This method will not allow 123456789,
unless you enter it as 0123456789. Dave's method will allow 123456789 and
then show as 0123456789. With his method though the result is still a
number. The result of mine will be a text string.


"CF" wrote:

> How can I format a cell so that ONLY ten numbers can be entered; no less than
> ten, and no more than ten can be entered. Also, some of these ten digit
> numbers begin with a zero and I would like that zero to display as well as
> count as one of the ten digits.
>
> Thanks in advance for your help!
>

[Dave Peterson]

But you also allow ABCDEABCDE (because it's just based on length of the text).

The OP could still format the cell as text, then use:
format|conditional formatting
formula is:
=AND(LEN(A1)=10,ISNUMBER(-A1))

But this still allows non-numeric entries (darn scientific notation!):
12341234E1
But that may not be a problem for the OP.




Sloth wrote:
>
> Format the cells as text first
> The select the Data menu and choose Validation
> Under Allow choose Text length
> Under Data choose Equal to
> Under Length type 10
> Click OK
>
> by numbers I assume you mean digits. This method will not allow 123456789,
> unless you enter it as 0123456789. Dave's method will allow 123456789 and
> then show as 0123456789. With his method though the result is still a
> number. The result of mine will be a text string.
>
> "CF" wrote:
>
> > How can I format a cell so that ONLY ten numbers can be entered; no less than
> > ten, and no more than ten can be entered. Also, some of these ten digit
> > numbers begin with a zero and I would like that zero to display as well as
> > count as one of the ten digits.
> >
> > Thanks in advance for your help!
> >

--

Dave Peterson