custom number format #10

is there a way to format a number so that:

if there are decimal places, you see: 1234.56  (which includes the "." 
character),
but,
 if it is a whole number, the display is: 1234 (without a decimal character, 
i.e. not "1234.")

##0.## is not correct, since it always displays the "." character...

thanks in advance,
mark 


0
mark8561 (7)
8/21/2006 4:40:05 PM
excel 39879 articles. 2 followers. Follow

1 Replies
422 Views

Similar Articles

[PageSpeed] 22

On Mon, 21 Aug 2006 12:40:05 -0400, "mark kubicki" <mark@tillotsondesign.com>
wrote:

>is there a way to format a number so that:
>
>if there are decimal places, you see: 1234.56  (which includes the "." 
>character),
>but,
> if it is a whole number, the display is: 1234 (without a decimal character, 
>i.e. not "1234.")
>
>##0.## is not correct, since it always displays the "." character...
>
>thanks in advance,
>mark 
>

You can format the cell as General and that will do as you describe.

However, if you always want two decimal places if the value is an integer, then
you can do this with an event-triggered macro.  I don't know any way to do that
using just Excel built-in functions.

To use an event macro, right-click on the sheet tab and select View Code.

Paste the code below into the window that opens.

Change the range for AOI to the range you wish to have conditionally formatted.

Let me know if one of the suggestions help.

==========================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim c As Range

Set AOI = [a1:a10]  'set to range to be conditionally formatted

Application.EnableEvents = False
On Error Resume Next 'only error should be if text entered in AOI
For Each c In AOI
    With c
        If Int(.Value) = .Value Then
            .NumberFormat = "General"
        Else
            .NumberFormat = "#,##0.00"
        End If
    End With
Next c
Application.EnableEvents = True
End Sub

=======================================
--ron
0
ronrosenfeld (3122)
8/21/2006 5:23:18 PM
Reply:

Similar Artilces:

Enter "0" is number is negative
I have a row that is calculating figures and some of numbers are positve and some are negative. How do I tell it to enter a zero, if the calculation returns a negative number and enter the positive number if the calculation is postive. one way: =IF(SUM(A1:H1)>0,SUM(A1:H1),0) Regards Trevor "S Girl" <anonymous@discussions.microsoft.com> wrote in message news:1b1d301c44ff1$784ad7e0$a601280a@phx.gbl... > I have a row that is calculating figures and some of > numbers are positve and some are negative. How do I tell > it to enter a zero, if the calculation re...

Sort window for Custom Sort gets bigger each use
Our company recently rolled out Office 2007 Standard. One user uses Excel quite a lot. She does the Custom Sort action a lot (under the Home tab>Sort&Filter button). She noticed that the window that pops up actually increases in size each time she clicks the Custom Sort button. When I try it on my pc, I can resize the Sort window, and have it remember whatever size I put it at each time, whether I close and restart Excel, or just doing another Sort action. For her, it increases exponentially. The process goes like this: 1. Click Custom Sort 2. Click Cancel to close Sort window. ...

Formats
I have a database imported into excel. The zip code displays as a 5 digit zip in the cell but as a 4 digit entry on the formula bar when there is a leading zero. The column has been formatted as a zip code. I use this in a mail merge and the zip codes do not display the leading zero in the address. Any ideas on how to fix this in XP version 2002. It worked fine in version 2000. Hi one way: Use a helper column with the following formula =TEXT(a1,"00000") copy down for all rows and use this helper column in your mail merge -- Regards Frank Kabel Frankfurt, Germany "Jan&qu...

inconsistency in phone numbers
Hello, my Outlook 2003 handles phone numbers in an inconsistent way. When specifying a number it checks and formats this number. But depending on the number value it handles it in different ways. Examples (typed number -> formated result by Outlook): '+1-1234-5678' -> '+1 (1234) 5678' '+49-1234-5678' -> '+49 1234-5678' '+77-1234-5678' -> '+77 (1234) 5678' My national code is +49, so I guess it just changes the local numbers inconsistently. Do you have any idea how to correct the behavior to handle all numbers the s...

Xml serialization 06-11-10
Hello! What does it mean what it says that you cannot serialize object graphs; you can use XML serialization only on objects ? //Tony Tony Johansson wrote: > What does it mean what it says that you cannot serialize object graphs; you > can use XML serialization only on objects ? I think that an object graph can contain cycles and that XmlSerializer cannot deal with cycles. -- Martin Honnen --- MVP Data Platform Development http://msmvps.com/blogs/martin_honnen/ "Martin Honnen" <mahotrash@yahoo.de> skrev i meddelandet news:OqUExkVC...

Formula to reach goal percentage of migrating customers
I am working on a formula and am stuck. I have a goal date that I want to have a certain percentage of customers migrated (90%). There are many weeks leading up to the goal date. I have the following criteria setup: Prior to Week-4 2% of base migrate Week-4 20% migrate Week-3 20% migrate Week-2 20% migrate Week-1 20% migrate Week-0 balance migrate How can I set up a formula on a declining base to reach my goal of 90%? ...

How do I prevent duplicate numbers in a range of validated cells?
I have a validation rule for a series of cells. The 3 cells are only allowed to have a value of 3, 5, or 1. I want to fix it so that each number may only be used once in a range of 3 cells. .... So in A1:A3, each number may only appear once or an error message pops up or the cell starts blinking ~ something needs to happen to notify the user that there is an error that needs correcting. THanks ...

Conditional Formatting in data table of a chart
I'm using Excel 2007, and trying to apply conditional formatting to the data table of a line chart. I've tried creating the chart without the formatting applied to cells with the original data (data source), and then applying the format to the source; as well as creating the chart after applying the format to the source. The data table in the chart will naturally update with changed data since it is linked, but will not incorporate any style formatting. Any thought? Thanks. Data tables in charts are not particularly flexible. They do not, for example, support conditional fo...

View column position or line number in Word 2007?
Ugh. I used to be ablet o see my horizontal column position in Word 2003 and I used to be ble to see the line I was on as well. Both were on the Status Bar. Did those features go away in Word 2007? I can find nothing in Help. Help seems to be very lacking in Word 2007. I am using Windows Xp. Thanks, Natelle Bracken San Diego Right click in the status bar to find those options. Pam Natelle wrote: >Ugh. I used to be ablet o see my horizontal column position in Word 2003 and >I used to be ble to see the line I was on as well. Both were on the Status >Bar. Did ...

How to retrieve old email after formatting pc
since windows vista infected virus that crash the windows system, i format my laptop and use windows 7. But i forget to backup my ms outlook 2007 email. Can anyone expert in here give an idea or any software that can retrieve back all my email, because it really important email its related with my work. for your info i use company email that out souce to webmail provider. Thank You. Do you have an older backup? Were copies of the mail stored on the server? Recovering it after a reformat is very tricky (and expensive) business and there's no guarantee it'll even work. ...

date formatting #15
I have a *.CSV file with a column of dates in various formats, such as - 1/01/1900 1/07/1901 1/06/1903 1880 1901 1904 1911 1911 1913 1/05/2004 Winter 2004 I need to get these dates into YYYYMMDD or YYYY format (depending on how much info is available, e.g. 1911 is OK left as 1911). Formatting the cells as custom YYYYMMDD however doesn't work, as ?pre-1900 dates get converted wrongly. There are ~20K lines in the file, so any solution has to be pretty much automatic. Your help would be very much appreciated! TIA bernieh One way, using a macro. It will place the new values in th...

accounting format for zero show 0.00 in one cell "-" in another
I have formatted cells with the accounting format with no symbol. Two cells that compute to zero show differently. One shows 0.00 and the other shows a "-" dash. I have rechecked all of the cells that are included in the formula for each and I cannot find any inconsistencies; all are formatted the same. I realize that this seems petty but for the life of me I cannot determine why the same formatting shows two different designations for zero. I prefer the "-" dash. Can anyone suggest what I can look at to try and resolve this? Thank you. If the cells contain fo...

Shading cell in custom colour
Can I shade a cell in a custom colour, or is there a bigger choice than the default choices (I want a lighter grey than the 25% grey) Thanks -- Turnipboy ------------------------------------------------------------------------ Turnipboy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24527 View this thread: http://www.excelforum.com/showthread.php?threadid=486869 XL can display only the 56 colors in the color palette. However, if you open the palette (Tools/Options/Color) and click Modify, you can replace any of the colors with a custom color. In articl...

search telephone numbers outlook 2007
Please help I cant seem to search for telephone numbers once they have been altered in a hyperlink ie when you type tel:0111122223 followed by either a space or return in an appointment. even using the advanced search criteria? can telephone number be searched? you could search for tel No's in earlier versions of outlook what am I doing wrong. Thank you KC ...

Display Name of customer from FILTERED DATA.
Hi, Is there anyway to GET data (Customer Name..currently filtered) from one of the column of FILTERED DATA.Suppose I filtered Data on Customer "A". I want to capture the Name of This Customer "A" and display this name on another cell(CellA1). If filtered data is changed and record is Filtered to Customer "B", cell A1 display "Customer B". Hope this time I made it clear Thanx in advance for your help Syed Syed, If you want a live link to the filtered data, I think not. Advanced filter, with the "Copy to another location" is generally...

How do I format an imported number 15034 to $150.34
If you just want to format it: Format/Cells/Number/Custom $0\.00 Note that this doesn't actually change the value. To do that requires either a function in another cell or a macro. In article <F0FEC5D1-33BD-42C9-AF5A-1570328F7FB9@microsoft.com>, "Format an imported number in Excel" <Format an imported number in Excel@discussions.microsoft.com> wrote: Could you instruct me as to how to insert a macro to complete this, or tell me where to learn? "JE McGimpsey" wrote: > If you just want to format it: > > Format/Cells/Number/Custom $0...

Accessing custom document properties
Anyone know of a way to access and display the custom document properties in Excel? Thanks ... ...

Business Cards #10
Can you print double sided business cards in Publisher? If so, can you tell how because I don't see that option. The way I do it in Publisher 2000 is create a two page card (front and back). Print page 1 of 1 then turn the card stock over and print page 2 of 2. Or, if you have a duplex printer print pages 1 and 2. -- Don Vancouver, USA "Mary" <Mary@discussions.microsoft.com> wrote in message news:72860FB0-69DE-4BE2-82E3-07587D9DF0F4@microsoft.com... > Can you print double sided business cards in Publisher? If so, can you tell > how because I don't see t...

passing custom field from lead to account on conversion
We are using CRM 3.0. We have a custom text field in our lead form/entity which we would like copied to a similar custom field in the account form/entity when a Lead is converted into an Account, Contact, Opportunity. Would someone be able to explain how to do this? Many thanks You need to add a mapping on the relationship between Lead and the other objects. This is done through the customization. Open the lead, then got o the relationships tab, find the one tied to the right objet and edit it. The mappings are on the relationship. -- Matt Parks MVP - Microsoft CRM "DMcL&q...

I can't set "Show in Microsoft CRM client for OutLook when offline" in custom entities
Hi, when I set the option "Show in Microsoft CRM client for OutLook when offline" in custom entities I get a generic error "An error has occurred. For further information, please contact system administrator". Could someone please help me to solve this problem? Thanks. Giacomo ...

Splitting Numbers in a Column #2
I certainly appreciate all the responses.... I have learned so much an thanks again... this site is so amazing.. -- jer10 ----------------------------------------------------------------------- jer101's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1076 View this thread: http://www.excelforum.com/showthread.php?threadid=26625 ...

Conversion/transform publisher format to PDF
How can I convert/transform my publisher format to PDF format mdnizar wrote: > How can I convert/transform my publisher format to PDF format In Publisher 2003 and below: Use PrimoPDF from www.primopdf.com, or one of the other PDF creators. In Publisher 2007: Download and install the "Save As PDF and XPS" add-in from microsoft.com. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Ed Bennett wrote: > mdnizar wrote: >> How can I convert/transform my publisher format to PDF format > > In Publisher 2003 and below: Use PrimoPDF from www.primopdf.com, or...

Number of Tabs in a workbook
I know that excel 2003 has no specific upper limit (that I can find... the old '95 limit of 255 is gone as I'm looking at one with over 1000 right now) my problem is that I've got two worksheets that should be identical copies of eachother one is allowing me to add 37 extra tabs (copies of a current tab) one allows me to add 30 extra tabs(again, copies of the same tab) when I try to copy a new tab through code, I get a copy method of class failed when I try to copy a new tab by hand it silently fails (i.e. no error message) any advice on the cause and a possible work around wou...

Numbering Pages in Manuscript
I have completed a book, but I cannot number the pages as I wish, like i, ii, iii, iv, 5, 6, 7, and so on. I await your expert responses. Thanks. Tom What version Publisher? Read the help on inserting a section. or Insert or remove a section break between pages in a publication http://office.microsoft.com/en-us/assistance/HP066360201033.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Thomas Minter" <thos.minter@sev.org> wrote in message news:12bt8bltaaiup3a@corp.supernews.com... >I have completed a book, ...

Edit Filter Criteria in Custom View
I have created a custom view in the contacts section. I need to filter out all companies with the name ABC. I have 120 ABC branches in the database. If I edit the filter criteria in this view, MS CRM will only return the first 100 records. Is there a way to return the extra 20 records or another way to get this data? Thanks in advance. look on the bottom right of the results view and you will see the word Page and an error on the right. Clicking this will display the other 20. assuming you mean filter in rather than filter out :-) -- John O'Donnell Microsoft CRM MVP http://ww...