VBA/Excel/Access/Word/Date Functions/Number Format

Characters for Creating Your Own Number Formats

 
        
Character      Explanation         
[None]         Displays the number without any formatting.         
0              Placeholder for a digit.         
#              Placeholder for a digit. If there"s no digit, VBA displays nothing.        
.              Placeholder for a decimal.         
%              Placeholder for a percent character. 
,              Thousand separator (depending on locale, a comma or a period).        
:              Time separator.        
/              Date separator. 
E- E+ e- e+    Scientific format: E- or e- places a minus sign next to negative exponents. E+ or e+ places a minus sign next to negative exponents and places a plus sign next to positive exponents.        
- + $ ()       Displays a literal character.        
\[character]   Displays the literal character.        
"[string]"     Displays the literal character.

Currency - The number is displayed with two decimal places, a thousand separator, and the currency symbol appropriate to the system locale.

 
Sub formatDemo3()
    Debug.Print Format("12345", "Currency")
End Sub

Fixed - The number is displayed with two decimal places and at least one integer place.

 
Sub formatDemo2()
    Debug.Print Format("12345", "Fixed")
End Sub

Format(dblNumber "#####0")

 
Sub numFormat()
    dblNumber = 12345.678
    Debug.Print dblNumber
    Debug.Print Format(dblNumber "#####0")
End Sub

Format(dblNumber "0.00")

 
Sub numFormat()
    dblNumber = 12345.678
    Debug.Print dblNumber
    Debug.Print Format(dblNumber "0.00")
End Sub

Format(dblNumber "00")

 
Sub numFormat()
    dblNumber = 12345.678
    Debug.Print dblNumber
    Debug.Print Format(dblNumber "000000.00")
End Sub

Format(dblNumber "###,##0.00"): double

 
Sub numFormat()
    dblNumber = 12345.678
    Debug.Print dblNumber
    Debug.Print Format(dblNumber "###,##0.00")
End Sub

General Number - The number is displayed with no thousand separator.

 
Sub formatDemo1()
    Debug.Print Format("12345", "General Number")
End Sub

On/Off - A non-zero number is displayed as On; a zero number is displayed as Off.

 
Sub formatDemo9()
    Debug.Print Format("12345", "On/Off")
End Sub

Percent - The number is displayed multiplied by ith two decimal places, and with a percent sign.

 
Sub formatDemo5()
    Debug.Print Format("12345", "Percent")
End Sub

returns a currency formatted with four decimal places:

 
Sub FormatDemo1()
    Debug.Print Format("123456", "$00.0000")
End Sub

Scientific - The number is displayed in scientific notation.

 
Sub formatDemo6()
    Debug.Print Format("12345", "Scientific")
End Sub

Standard - The number is displayed with two decimal places, at least one integer place, and a thousand separator (when needed).

 
Sub formatDemo4()
    Debug.Print Format("12345", "Standard")
End Sub

True/False - A non-zero number is displayed as True; a zero number is displayed as False.

 
Sub formatDemo8()
    Debug.Print Format("12345", "True/False")
End Sub

Yes/No - A non-zero number is displayed as Yes; a zero number is displayed as No.

 
Sub formatDemo7()
    Debug.Print Format("12345", "Yes/No")
End Sub