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 ';':

 

Parameter

Description

#

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

Example: Format(19,"###") returns "19"

Example: Format(19,"#") returns "19"

0

Digit, include leading/trailing zero digits.

Example: Format(19,"000") returns "019"

Example: Format(19,"0") returns "19"

.

Decimal, insert localized decimal point.

Example: Format(19.9,"###.00") returns "19.90"

Example: 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.

Example: Format(1900000,"0,,") returns "2"

Example: Format(1900000,"0,,.0") returns "1.9"

%

Percent, insert %, multiply expr by 100 prior to formatting.

:

Returns "No" if zero, else returns "Yes".

/

Insert localized date separator.

E+ e+ E- e-

Use exponential notation, insert E (or e) and the signed exponent.

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

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

- + $ ( ) space

Insert literal char

Example: Format(10,"$#") returns "$10."

\c

Insert character c.

Example: Format(19,"\####\#") returns "#19#"

"text"

Insert literal text.

Example: Format(19,"""##""###""##""") returns "##19##"

 

 

Example:


Sub Main
  Debug.Print Format$(2.145,"#.00") ' 2.15
End
Sub