Show a "+" along with a Postive number

[Skybluekid]

Hi All,

I am looking to show "+" when a positive number and a "-" for a Negative number. I hear you shout Customer number formatting! Yes, that would do the job, but I would like the "+" to stay in one place whether there is 3 digit number or 1. I have tried using the Text function, but the "+" still moves.

Any ideas would be appreciated.

Thanks in Advance

 

[Alex Blakenburg]

Is this custom format close to what you want ?

+* #,##0_-;-* #,##0_-;_-"-"_-;_-@_-

 

[Skybluekid]

Thank you. Would it be possible to explain how it works?

 

[Alex Blakenburg]

Sure.
An asterisk in front of a character tells the custom format to repeat that character.
It repeats that character in such a way so as to together with the other characters displayed, pad it out to fit the column width.
A common use is to have the $ sign at the far left.
So in the format we are saying

+ --> Start with this character​

*<space> --> repeat the space as many times as required to fill the column after including the other characters specified.​

#,##0 --> the numeric component showing a thousands separator of comma and no decimal places​

_ --> Underscore = leave a gap the size of which fits the next character​

- --> Minus sign > preceded by the underscore - only give the character size of the gap to leave - not actually displayed.​

Let me know if I didn't explain it well.

References:
Custom Excel number format
https://www.myonlinetraininghub.com/excel-custom-number-format-guide#_Toc474757773

 

[Skybluekid]

Thank you very much.

 

[Alex Blakenburg]

Glad I could help. Thanks for the feedback.