Suppressing zeros after the decimal point - Excel 2002

[Guest]

How can I force zeros to display following a decimal point in Excel 2002? Whenever I type a number, such as 3.200, Excel drops the zeros and only enters 3.2 into the cell. Is there a setting that can be changed to fix this? (Using the "0.00" custom format seems to help, but if I have another number, such as 4.70, which only needs one zero to display, then I need another custom format for that one, and the next one, etc.) If anyone has a simpler suggestion, I'd appreciate it. Thanks!

 

[Frank Kabel]

Hi
AFAIK you can't achieve this with a custom format. Either you have
three decimals or Excel will skip the zeros

 

[Guest]

My suggestion would be to set the cell format to Text, then whatever you type in will remain as is. However, it is more difficult to perform calculations on text formatted cells as you often need to convert the cell contents to values in order to perform calculations on them

----- leb wrote: ----

How can I force zeros to display following a decimal point in Excel 2002? Whenever I type a number, such as 3.200, Excel drops the zeros and only enters 3.2 into the cell. Is there a setting that can be changed to fix this? (Using the "0.00" custom format seems to help, but if I have another number, such as 4.70, which only needs one zero to display, then I need another custom format for that one, and the next one, etc.) If anyone has a simpler suggestion, I'd appreciate it. Thanks!

 

[Debra Dalgleish]

You can type an apostrophe before the number, and it will be entered as
text, and retain the number of decimals entered, e.g. '3.200

These text numbers would be recognized in many formulas, e.g. =A1+B1
but would require extra manipulation for some functions, such as SUM.

 

[Bernie Deitrick]

leb,

Certainly not simple, but you could use a worksheet event to achieve that.
Copy the code below, right click on the sheet tab, select "View Code" and
paste in the window that appears. Format column A as text, then enter your
numbers with as many zeroes as you want. The event will convert the string
enetered to a number with the same number of trailing zeroes, and if you
enter a formula, it will format the formula for 3 decimal places.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myVal As String
Dim myFormat As String
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Then
myVal = Target.Text
If myVal = "" Then Exit Sub
Application.EnableEvents = False
If Left(myVal, 1) <> "=" Then
If InStr(1, Target.Text, ".") <> 0 Then
myFormat = "0." & Application.Rept("0", Len(Target.Text) _
- InStr(1, Target.Text, "."))
Else
myFormat = "0"
End If
Target.NumberFormat = myFormat
Target.Value = Val(myVal)
Else
Target.NumberFormat = "0.000"
Target.Formula = myVal
End If
Application.EnableEvents = True
End If
End Sub

How can I force zeros to display following a decimal point in Excel 2002?

Whenever I type a number, such as 3.200, Excel drops the zeros and only
enters 3.2 into the cell. Is there a setting that can be changed to fix
this? (Using the "0.00" custom format seems to help, but if I have another
number, such as 4.70, which only needs one zero to display, then I need
another custom format for that one, and the next one, etc.) If anyone has a
simpler suggestion, I'd appreciate it. Thanks!

 

[tghcogo]

You have to format the cells. On the format toolbar are two buttons
one to increase and one to decrease the number of zeros displayed in
cell.

If they are not already displayed:-

Right mouseclick with the pointer over the toolbar (not over a button)
and choose customize from the pull down menu.

select the "Command" tab and you will see a list of available toolbar
displayed. Highlight format and you will see on the right hand side
list of available buttons for that toolbar. Use the right hand scrol
bar to show the "increase decimal" button. just drag it up and drop i
onto the toolbar. You might as well do the same with the decreas
decimal button while you are there.



TGHCOG

 

[tghcogo]

oops:

I need to pay more attention to threads, my previous post relates t
suppressing zeros - question at the beginning of this thread.

i.e

"How can I force zeros to display following a decimal point in Exce
2002? Whenever I type a number, such as 3.200, Excel drops the zero
and only enters 3.2 into the cell. Is there a setting that can b
changed to fix this? (Using the "0.00" custom for
mat seems to help, but if I have another number, such as 4.70, whic
only needs one zero to display, then I need another custom format fo
that one, and the next one, etc.) If anyone has a simpler suggestion
I'd appreciate it. Thanks!"


Cheers

TGHCOG