Number Formats for the format function

Character

Description

None

Display the number with no formatting.

 0

Digit placeholder. Display a digit or a zero. If the expression has a digit in the position where the 0 appears in the format string, display it; otherwise, display a zero in that position.
Leading / trailing zeros for these digits are displayed.

 #

Digit placeholder. Display a digit or nothing. If the expression has a digit in the position where the # appears in the format string, display it; otherwise, display nothing in that position.
Leading / trailing zeros for these digits are suppressed.

 .

Decimal placeholder. In some locales, a comma is used as the decimal separator.

 %

Percentage placeholder. The expression is multiplied by 100.

 ,

Thousand separator. In some locales, a period is used as a thousand separator.
Two adjacent thousand separators or a thousand separator immediately to the left of the decimal separator (whether or not a decimal is specified) means "scale the number by dividing it by 1000, rounding as needed." For example, you can use the format string "##0,," to represent 100 million as 100. Numbers smaller than 1 million are displayed as 0. Two adjacent thousand separators in any position other than immediately to the left of the decimal separator are treated simply as specifying the use of a thousand separator.

 :

Time separator. In some locales, other characters might be used to represent the time separator.

 /

Date separator. In some locales, other characters might be used to represent the date separator.

(E- E+ e- e+)

Scientific format. If the format expression contains at least one digit placeholder (0 or #) to the right of E-, E+, e-, or e+, the number is displayed in scientific format and E or e is inserted between the number and its exponent.

- + $ ( )

Display a literal character. To display a character other than one of those listed, precede it with a backslash (\) or enclose it in double quotation marks (" ").

 \

Display the next character in the format string. To display a character that has special meaning as a literal character, precede it with a backslash (\). The backslash itself isn't displayed. Using a backslash is the same as enclosing the next character in double quotation marks. To display a backslash, use two backslashes (\\).

Examples of characters that can't be displayed as literal characters are the date-formatting and time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y, / and :), the numeric-formatting characters (#, 0, %, E, e, comma, and period), and the string-formatting characters (@, &, <, >, and !).

 "ABC"

Display the string inside the double quotation marks (" "). To include a string in format using VBA code, use Chr(34) to enclose the text in quotes (34 is the character code for a quotation mark).