![]() |
#1
|
|||
|
|||
![]()
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. ![]() |
#2
|
||||
|
||||
![]()
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 |
#3
|
|||
|
|||
![]()
Unfortunately, Excel 2003 doesn't permit custom format in the conditional formatting dialog.
![]() |
#4
|
||||
|
||||
![]()
Hi Scaffold,
You could employ a UDF in the relevant worksheet's code module, coded along the lines of: Code:
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
foxtrot | Word | 3 | 07-24-2012 07:30 AM |
Adding comma to a custom type. | mcook | Excel | 1 | 08-13-2011 03:08 AM |
adding a comma in end of each line in word | juanb007 | Word | 0 | 07-23-2010 01:28 PM |
![]() |
taylormayd | Excel | 3 | 02-20-2009 03:46 AM |
![]() |
kevin_ms | Excel | 4 | 09-14-2008 10:08 PM |