Excel Custom Formatting, 0, # and ? characters

In this article I will explain the characters “0”, “#” and “?” used in custom formats.


“0” Character:

The “0” is a placeholder for digits. On the left hand side of the decimal:

  1. If there aren’t enough digits to fill the placeholders, zeros will be added to the number.
  2. If there are more digits than the placeholders the digits will be displayed as they are.

Example:

00000

As you can see in the figure below the number 123 has only 3 digits, which is not enough to fill the 5 zeros in the custom format, therefore a 2 zeros were added to the start of the number.

Result:

Excel, Custom Formatting, Digit Place Holders
On the right side of the decimal place:

  • Similar to the right side if there aren’t enough digits to fill the placeholders, zeros will be added to the number.
  • If there are more digits than the placeholders the digits won’t be displayed.

Example:

0.0000

As you can see in the figure below the first number only has 1 digit after the decimal point therefore 3 zeros were added to accommodate for the placeholders. The number 123.12345 on the other hand has 5 digits after the decimal point but the custom format only has 4 placeholders, therefore only 4 digits are displayed:

Excel, Custom Formatting, Digit Place Holders 1


“#” Character:

The “#” is similar to the “0” with the differences that it doesn’t add zeros when there aren’t enough digits in the number to fill the placeholders.

Example:

These will all yield the same result:

#

##

###

#######

Result:

Excel, Custom Formatting, Digit Place Holders 2
Example:

Similar to the “0” character on the right side of the decimal point only the number of digits specified by the placeholders will be displayed. If there are less digits than the placeholders, zeros will not be added to accommodate for the # character.

#.###

Result:

Excel, Custom Formatting, Digit Place Holders 3

Example:

In this example a comma character will separate the digits after every 3 digits:

#,#

Result:

Excel, Custom Formatting, Digit Place Holders 4


“?” Character:

The “?” character is similar to the “0” character with the difference that instead of adding zeros when there are less digits than the number of digit placeholders, it adds spaces. This will cause the numbers to align horizontally based on the location of the decimal point.

Example:

?????.?????

Result:

Excel, Custom Formatting, Digit Place Holders 6

As it can be seen from the figure above all the numbers are aligned based on the location of the decimal point.

You can download the file related to this article from the link below

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *

privacy policy