Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to define a custom number format to simply prepend + or - to positive and negative numbers, and I want to use the default number formatting that only includes as many places or decimals as necessary.

For example general format:

8
-5.25
6.5
-2.75

would instead be displayed as:

+ 8
- 5.25
+ 6.5
- 2.75

The closest I've gotten so far is + #.###; - #.###;, but this still attached trailing decimal points to whole numbers. How can I just drop in the general number format after the sign?

share|improve this question
    
I think you'll have a hard time eliminating the trailing decimal point with your other constraints. All of the Excel number wildcards seem to leave them. – dav May 23 '13 at 19:46
up vote 6 down vote accepted

You can use custom format

+ General;- General

enter image description here

This also works in Excel 2003

enter image description here

and in Excel 2007

enter image description here

and in 2013

enter image description here

I don't have ready access to any earlier versions but I don't doubt that this will also work in Excel 2000 and previous.

share|improve this answer
    
YES!!! Awesome. – NReilingh May 23 '13 at 21:00
1  
+1 Nice solution to an issue that people have gone through all sorts of contortions to address (e.g., worksheet change event macro). Do you know whether it works in older (pre-2010 or 2007) versions of Excel? – chuff May 23 '13 at 22:22
    
I see no reason why this would not work in any current version. Confirmed and tested in 2003 and 2010. Need to find a box with 2007 and 2013. – teylyn May 24 '13 at 3:06
    
tested and added screenshots for 2007 and 2013. – teylyn May 24 '13 at 10:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .