Excel Custom Formatting, 0, # and ? characters
In this article I will explain the characters “0”, “#” and “?” used in custom formats.
Contents
“0” Character:
The “0” is a placeholder for digits. On the left hand side of the decimal:
- 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 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:
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:
“#” 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:
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:
Example:
In this example a comma character will separate the digits after every 3 digits:
#,#
Result:
“?” 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:
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
One thought on “Excel Custom Formatting, 0, # and ? characters”