Share via

Custom Number Format Codes

Anonymous
2016-11-07T22:11:19+00:00

I am trying to better understand how custom formats work, so that I can more easily create my own.  Therefore, I am wondering what the following characters mean in the context of a custom format:  underscore, left or right parenthesis, asterisk, semi-colon, ampersand and square brackets?

For example, what does the following (pre-defined) custom format do? 

_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)

In particular, I am having trouble understanding the following:

(1)  I know that a semi-colon separates different cases, and I have seen how it can be used to format positive numbers differently than negative numbers, but what are the other two cases here?

(2)  I thought asterisks couldn't be used at all in a format.  What does it mean?

(3)  It almost looks like "_(" and "_)" are being used as groups rather than individual characters, but it isn't at all clear what they do.

(4)  Can square brackets be used for anything besides changing font colour and creating "conditional" formats?

It would be very helpful if there were a "complete" primer somewhere to describe the more complex formatting codes, but anything I can find in the on-line help only scratches the surface.

Thanks for any help you can provide!

Regards,

Hugh John

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2016-11-07T23:03:12+00:00

(1) The four sections of a custom number format are:

positive;negative;zero;text

(2) An asterisk * followed by a character means: repeat that character as often to fill the cell.

For example the format   $* 0.00   will left align the $ sign and right-align the number (with 2 decimal places), because the $ sign will be followed by as many spaces as needed to fill the cell.

(3) An underscore _ followed by a character creates a space the width of that character.

In the example that you mention, negative numbers are enclosed in parentheses ( ). The _) at the end of the section for positive numbers creates a space the width of a ) after the number, causing the last digit of positive numbers to be aligned with the last digit of negative numbers.

(4) There is one other use for square brackets: in a time format, [h] stands for cumulative hours, [m] for cumulative minutes and [s] for cumulative seconds. For example, the format h:mm will display times up to 23:59, but [h]:mm can display 28:04 or 132:45.

See:

http://www.exceltactics.com/definitive-guide-custom-number-formats-excel/

https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4

http://exceldesignsolutions.com/a-comprehensive-guide-number-formats-excel/

Was this answer helpful?

30+ people found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-11-08T13:54:48+00:00

    Thanks, Ossie -- that link is brilliant!

    Regards,

    HJ

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-11-08T13:53:44+00:00

    Thanks, Hans -- you have answered my questions very clearly and the link has everything I need to know about custom formats!

    Regards,

    HJ

    Was this answer helpful?

    0 comments