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

Characters for Creating Your Own Number Formats

   <source lang="vb">

       

Character Explanation [None] Displays the number without any formatting. 0 Placeholder for a digit.

1. 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.

</source>
   
  

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

   <source lang="vb">

Sub formatDemo3()

   Debug.Print Format("12345", "Currency")

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo2()

   Debug.Print Format("12345", "Fixed")

End Sub

</source>
   
  

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

   <source lang="vb">

Sub numFormat()

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

End Sub

</source>
   
  

Format(dblNumber "0.00")

   <source lang="vb">

Sub numFormat()

   dblNumber = 12345.678
   Debug.Print dblNumber
   Debug.Print Format(dblNumber "0.00")

End Sub

</source>
   
  

Format(dblNumber "00")

   <source lang="vb">

Sub numFormat()

   dblNumber = 12345.678
   Debug.Print dblNumber
   Debug.Print Format(dblNumber "000000.00")

End Sub

</source>
   
  

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

   <source lang="vb">

Sub numFormat()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo1()

   Debug.Print Format("12345", "General Number")

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo9()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo5()

   Debug.Print Format("12345", "Percent")

End Sub

</source>
   
  

returns a currency formatted with four decimal places:

   <source lang="vb">

Sub FormatDemo1()

   Debug.Print Format("123456", "$00.0000")

End Sub

</source>
   
  

Scientific - The number is displayed in scientific notation.

   <source lang="vb">

Sub formatDemo6()

   Debug.Print Format("12345", "Scientific")

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo4()

   Debug.Print Format("12345", "Standard")

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo8()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub formatDemo7()

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

End Sub

</source>