Problem with custom number format.

  • Thread starter Thread starter Israel.V
  • Start date Start date
I

Israel.V

Hi all, I have a problem. My worksheet contains values like 12345.25,
233.34 , 77 . How can I format these cells to display 12,345.25,
233.34, 77 (not 77.0 or 77.). I tried with #,###0.## but always puts
the dot even there are not decimal values.

Any help will be highly appreciated.

Israel.
 
Hi,
I've always used two formats when I've had similar problems before, one
format for the numbers with decimal places and one without. To make
formatting them faster you could always create styles that used the two
custom formats or use format painter.
Regards,
Duncan
 
Another option would be to use an event macro.

If the data changes by the user typing the value into the cell, you could use
something like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("b3:b99")) Is Nothing Then Exit Sub
If Target.HasFormula Then Exit Sub

If Application.IsNumber(Target.Value) Then
If CLng(Target.Value) = Target.Value Then
'whole number
Target.NumberFormat = "#,###_._0_0"
Else
'decimal
Target.NumberFormat = "#,###.00"
End If
Else
Target.NumberFormat = "General"
End If

End Sub

Right click on the worksheet tab that should have this behavior. Then select
view code and paste this in.

Adjust the range to what you need. I used B3:B99 in my sample.
 
Back
Top