How do I format cells to a specific number of digits?

I am trying to format a column so I can enter 5 digits only. It has between 5
and 7 digit numbers in it right now and I need to delete the remaining ones.
It is about 350 numbers, so it would be a lot of work to go into each cell
and delete the remaning numbers. Is therea way I can format the whole column
to allow 5 digits? The same happened with letters. I had codes in it that
consisted of numbers and letters and the client only wants the first two
letters to remain. How can it be formatted so only 2 characters are allowed
to keep me from having to enter each cell separately?

Hi

formatting won't solve your problem - but i can be done using a "helper"
column
say your numbers are in column A in B1 type
=left(A1,5)
move the mouse over the bottom right hand corner of cell B1 and your mouse
should change to a + now double click and the formula will be filled down
for you. Click on the letter B to select the column and choose copy, click
on cell A1 and choose edit / paste special - values - click OK and now
delete column B

Cheers
JulieD

"Gabriele" <[email protected]> wrote in message
news:[email protected]...
>I am trying to format a column so I can enter 5 digits only. It has between
>5
> and 7 digit numbers in it right now and I need to delete the remaining
> ones.
> It is about 350 numbers, so it would be a lot of work to go into each cell
> and delete the remaning numbers. Is therea way I can format the whole
> column
> to allow 5 digits? The same happened with letters. I had codes in it that
> consisted of numbers and letters and the client only wants the first two
> letters to remain. How can it be formatted so only 2 characters are
> allowed
> to keep me from having to enter each cell separately?

Gabriele,

For the numbers already in the column, in a helper column:

=left(A2, 5) or
=right(A2, 5)

Or for the case where you want only the first two characters:

=left(A2, 2).

Copy down with the Fill Handle. Now to permanently remove the original
stuff, copy the helper column, then directly over the original stuff, Edit -
Paste special - Values. Now you don't need the helper column any more.

Your post hinted at preventing entering more than five characters. For
that, use Data - Validation, and select Text Length and "equal to." . Set
it to 5. Or something similar
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Gabriele" <[email protected]> wrote in message
news:[email protected]...
>I am trying to format a column so I can enter 5 digits only. It has between
>5
> and 7 digit numbers in it right now and I need to delete the remaining
> ones.
> It is about 350 numbers, so it would be a lot of work to go into each cell
> and delete the remaning numbers. Is therea way I can format the whole
> column
> to allow 5 digits? The same happened with letters. I had codes in it that
> consisted of numbers and letters and the client only wants the first two
> letters to remain. How can it be formatted so only 2 characters are
> allowed
> to keep me from having to enter each cell separately?

oh, i forgot to add - once you've changed all the values to 5 characters -
you can then limit what can be entered into the cell using data /
validation - choose the column, choose data / validation / allow - whole
numbers, less than 100000
this will then stop numbers with 7 digit from begin entered.

Cheers
JulieD


"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> formatting won't solve your problem - but i can be done using a "helper"
> column
> say your numbers are in column A in B1 type
> =left(A1,5)
> move the mouse over the bottom right hand corner of cell B1 and your mouse
> should change to a + now double click and the formula will be filled down
> for you. Click on the letter B to select the column and choose copy,
> click on cell A1 and choose edit / paste special - values - click OK and
> now delete column B
>
> Cheers
> JulieD
>
> "Gabriele" <[email protected]> wrote in message
> news:[email protected]...
>>I am trying to format a column so I can enter 5 digits only. It has
>>between 5
>> and 7 digit numbers in it right now and I need to delete the remaining
>> ones.
>> It is about 350 numbers, so it would be a lot of work to go into each
>> cell
>> and delete the remaning numbers. Is therea way I can format the whole
>> column
>> to allow 5 digits? The same happened with letters. I had codes in it that
>> consisted of numbers and letters and the client only wants the first two
>> letters to remain. How can it be formatted so only 2 characters are
>> allowed
>> to keep me from having to enter each cell separately?
>
>