Format User Defined Number
Description:
The following number formats may be used with the Format
function. Number formats may be combined to create the user defined number
format. User defined number formats may not be combined with other user
defined formats or predefined formats.
User defined number formats can contain up to four sections separated by
';':
•form - format for non-negative
expr, '-'format for negative expr, empty and null expr return ""
•form;negform - negform: format
for negative expr
•form;negform;zeroform - zeroform:
format for zero expr
•form;negform;zeroform;nullform
- nullform: format for empty or null expr
Parameter
Description
# digit, don't include leading/trailing
zero digits (all the digits left of decimal point are returned)
eg. Format(19,"###") returns "19"
eg. Format(19,"#") returns "19"
0 digit, include leading/trailing
zero digits
eg. Format(19,"000") returns "019"
eg. Format(19,"0") returns "19"
. decimal, insert localized decimal
point
eg. Format(19.9,"###.00") returns "19.90"
eg. Format(19.9,"###.##") returns "19.9"
, thousands, insert localized thousand
separator every 3 digits
"xxx," or "xxx,." mean divide expr by 1000 prior to
formatting
two adjecent commas ",," means divide expr by 1000 again
eg. Format(1900000,"0,,") returns "2"
eg. Format(1900000,"0,,.0") returns "1.9"
% percent, insert %, multiply expr
by 100 prior to formatting
: insert localized time separator
/ insert localized date separator
E+ e+ E- e- use exponential notation,
insert E (or e) and the signed exponent
eg. Format(1000,"0.00E+00") returns "1.00E+03"
eg. Format(.001,"0.00E+00") returns "1.00E-03"
- + $ ( ) space insert literal char
eg. Format(10,"$#") returns "$10"
\c insert character c
eg. Format(19,"\####\#") returns "#19#"
"text" insert literal text
eg. Format(19,"""##""###""##""")
returns "##19##"
Example:
Sub Main
Debug.Print Format$(2.145,"#.00")
' 2.15
End Sub
