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 a semi-colon (;):

  • 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 null expr

    Parameters Description
    #

    Digit, don't include leading/trailing zero digits (all the digits left of decimal point are returned). For example:

    • Format(19,"###") returns "19"

    • Format(19,"#") returns "19"

    0

    Digit, include leading/trailing zero digits. For example:

    • Format(19,"000") returns "019"

    • Format(19,"0") returns "19"

    .

    Decimal, insert localized decimal point. For example:

    • Format(19.9,"###.00") returns "19.90"

    • 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 adjacent commas ",," means divide expr by 1000 again. For example:

    • Format(1900000,"0,,") returns "2"

    • 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. For example:

    • Format(1000,"0.00E+00") returns "1.00E+03"

    • Format(.001,"0.00E+00") returns "1.00E-03"

    - + $ ( ) space Insert literal char. For example, Format(10,"$#") returns "$10".
    \c Insert character c. For example, Format(19,"\####\#") returns "#19#".
    "text" Insert literal text. For example, Format(19,"""##""###""##""") returns "##19##". 
Sub Main
    Debug.Print Format$(2.145,"#.00") ' 2.15
End Sub