Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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" wrote in message ... 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? |
#3
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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? |
#4
|
|||
|
|||
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Least number of digits in Y-axis labels | Charts and Charting in Excel | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
Count number of shaded cells | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |