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?
0
Gabriele (5)
2/5/2005 3:03:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
465 Views

Similar Articles

[PageSpeed] 44

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" <Gabriele@discussions.microsoft.com> wrote in message 
news:F10AAE62-5620-4C84-BF4F-3E9C1EB8AE5F@microsoft.com...
>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? 


0
JulieD1 (2295)
2/5/2005 3:08:05 PM
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" <JulieD@hctsReMoVeThIs.net.au> wrote in message 
news:uDm1VS5CFHA.624@TK2MSFTNGP15.phx.gbl...
> 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" <Gabriele@discussions.microsoft.com> wrote in message 
> news:F10AAE62-5620-4C84-BF4F-3E9C1EB8AE5F@microsoft.com...
>>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?
>
> 


0
JulieD1 (2295)
2/5/2005 3:15:37 PM
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" <Gabriele@discussions.microsoft.com> wrote in message 
news:F10AAE62-5620-4C84-BF4F-3E9C1EB8AE5F@microsoft.com...
>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? 


0
nothanks4548 (968)
2/5/2005 3:17:26 PM
Reply:

Similar Artilces: