Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-18-2011, 10:33 PM
Scaffold Scaffold is offline Comma style without trailing zeroes but with decimal part Windows XP Comma style without trailing zeroes but with decimal part Office 2003
Novice
Comma style without trailing zeroes but with decimal part
 
Join Date: Oct 2009
Location: Moscow, Russia
Posts: 28
Scaffold is on a distinguished road
Default 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.
Attached Images
File Type: jpg excel zero.JPG (38.8 KB, 12 views)
Reply With Quote
  #2  
Old 10-19-2011, 07:52 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Comma style without trailing zeroes but with decimal part Windows 7 32bit Comma style without trailing zeroes but with decimal part Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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
Reply With Quote
  #3  
Old 10-19-2011, 10:58 PM
Scaffold Scaffold is offline Comma style without trailing zeroes but with decimal part Windows XP Comma style without trailing zeroes but with decimal part Office 2003
Novice
Comma style without trailing zeroes but with decimal part
 
Join Date: Oct 2009
Location: Moscow, Russia
Posts: 28
Scaffold is on a distinguished road
Default

Unfortunately, Excel 2003 doesn't permit custom format in the conditional formatting dialog.
Reply With Quote
  #4  
Old 10-22-2011, 11:08 PM
macropod's Avatar
macropod macropod is online now Comma style without trailing zeroes but with decimal part Windows 7 64bit Comma style without trailing zeroes but with decimal part Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,057
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
The above will apply your formatting to any cell in the A2:C2 range. Adjust to suit your needs.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 05:13 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft