Comma style without trailing zeroes but with decimal part

[Scaffold]

Greetings!

Is it possible to set up Excel to display comma style without trailing zeroes, but with decimal part of the figure, where it is non-zero?



I included an example of what I mean, made in MS Paint.

[Colin Legg]

Hi,

Yes, it is. The custom format you're looking for is:

#,##0.00

and this will always display 2 decimal places.

So that decimal places are not shown when the cell contains a whole number you could use a conditional formatting rule with a formula like this:
=A1=TRUNC(A1)

and setting the conditional formatting custom format as #,##0

[Scaffold]

Unfortunately, Excel 2003 doesn't permit custom format in the conditional formatting dialog.

[macropod]

Hi Scaffold,

You could employ a UDF in the relevant worksheet's code module, coded along the lines of:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static oCell As Range, bVal As Boolean
If oCell Is Nothing Then Set oCell = ActiveCell
If Intersect(oCell, ActiveSheet.Range("A2:C2")) Is Nothing Then Exit Sub
With oCell
  If IsNumeric(.Value) = False Then Exit Sub
  bVal = (CLng(.Value) = Val(.Value))
  If bVal = True Then
    .NumberFormat = "#,##0"
  Else
    .NumberFormat = "#,##0.00"
  End If
End With
End Sub

The above will apply your formatting to any cell in the A2:C2 range. Adjust to suit your needs.