#1
|
|||
|
|||
Comma style without trailing zeroes but with decimal part
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 Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Applying style to first part of paragraph | 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 |
thousands comma separator | taylormayd | Excel | 3 | 02-20-2009 03:46 AM |
How to stop correcting zeroes in cells ? | kevin_ms | Excel | 4 | 09-14-2008 10:08 PM |