Excel: Using Underscores in Custom Number Formats

 Last reviewed: November 22, 1994
Article ID: Q86530
The information in this article applies to:
  • Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0
  • Microsoft Excel for OS/2, version 3.0

SUMMARY

In Microsoft Excel, when you create a custom number format that contains an even number of underscore characters after the digit placeholders followed by another character such as a closing parenthesis, the character will appear in the formatted number.

CAUSE

In Microsoft Excel versions 3.0 and later, one of the special formatting characters used in a custom number format is the underscore character "_" (without the quotation marks). This character acts as a placeholder, leaving a space in the number format that is the width of the character that follows. For example, a custom number format of "#,##0_)" will leave a space that is exactly the width of the parentheses at the end of the number. This formatting is useful when displaying negative numbers in parentheses and also desiring the digits to align with positive numbers in the same column.

Using an even number of underscores does not leave extra space in the number format equal to the width of the last character in the format. When Excel encounters the first underscore character, it will leave space in the number format for the following character. Therefore, if there are two underline characters followed by ")", the width of the second underline character is left after the digits and the ")" will be displayed.

WORKAROUND

To prevent the ")" from appearing in the format and add extra spaces, use an odd number of underscore characters.

STATUS

This behavior is by design.

MORE INFORMATION

The amount of space left by the underscore character is dependent upon the font being used. If the font is proportional such as Microsoft SanSerif or Helvetica, the characters "W" and ")" will leave different size spaces. If the font is non-proportional, such as Courier, the two characters will leave the same size spaces.

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, page 226

"Microsoft Excel User's Guide," version 3.0, page 192

KBCategory: kbusage
KBSubcategory:

Additional reference words: 5.00 4.00 underscore underline 4.0

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 22, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.