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

[Guest]

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?

 

[JulieD]

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

 

[JulieD]

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

 

[Earl Kiosterud]

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