Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

up vote 5 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.