Fancy Formatting with Format | OpenOffice.org Macros Explained

You can convert a number to a string formatted according to the optional format string. And you can include multiple formats in a single format string. See Table 5 . The current locale influences the returned formatted string. Set the locale using Tools Options Language Settings Languages. If the format string is omitted, Format produces output similar to the CStr function.

Table 5: Format specifiers for the Format function.
Code	Description
	If the number has a digit at the position of the 0 in the format code, the digit is displayed; otherwise a zero appears. This means that leading and trailing zeros are displayed, leading digits are not truncated, and trailing decimals are rounded.
#	This works like the 0, but leading and trailing zeros are not displayed.
.	The decimal placeholder determines the number of decimal places to the left and right of the decimal separator. Although the period is used in the format string regardless of locale, the output correctly uses the locale-specific decimal separator.
%	Multiply the number by 100 and insert the percent sign (%) where it appears in the format code.
E- E+ e- e+	If the format code contains at least one numerical digit placeholder (0 or #) to the right of the symbol, the number is formatted in the scientific notation. The letter E or e is inserted between the number and the exponent. The number of placeholders for digits to the right of the symbol determines the number of digits in the exponent. If the exponent is negative, a minus sign (-) is displayed directly before an exponent. If the exponent is positive, a plus sign (+) is only displayed before exponents with E+ or e+.
,	The comma is a placeholder for the thousands separator. It separates thousands from hundreds in a number with at least four digits. The thousands delimiter is displayed if the format code contains the placeholder surrounded by digit placeholders (0 or #).
- + $ ( ) space	Plus signs (+), minus signs (-), dollar signs ($), spaces, or brackets entered directly in the format code are displayed as the literal character.
\	The backslash displays the next character in the format code. In other words, it prevents the next character from being seen as a special character. The backslash is not displayed unless you enter a double backslash (\\) in the format code. Characters that must be preceded by a backslash in the format code in order to be displayed as literal characters are the date- and time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y, /, :), numeric-formatting characters (#, 0, %, E, e, comma, period), and string-formatting characters (@, &, <, >, !). You may also enclose characters in double quotation marks.
General Number	Numbers are displayed as entered.
Currency	A currency symbol is placed in front of the number, and negative numbers are in brackets.
Fixed	At least one digit is displayed in front of the decimal separator. Two decimals are displayed.
Percent	Multiply the number by 100 and append a percent sign (%).
Standard	Displays numbers with a locale-specific thousands separator. Two decimals are displayed.
Scientific	Displays numbers in scientific notation. Two decimals are displayed.

 Print Format(1223, "00.00"  )  '1223.00 Print Format(1234.56789, "###00.00") '1234.57

Each individual format is separated by a semicolon (;). The first format is used for positive numbers, the second for negative numbers, and the third for zero. If only one format code is present, it applies to all numbers.

 Dim s As String s = "P 00000.000;N ####.00;Z 0.0" Print Format(-12.3, s)  'N 12.30 Print Format(0, s)      'Z 0.0 Print Format(12.3, s)   'P 000012.300

The Format function has numerous bugs that are slated to be fixed for OOo version 2.0. If you choose to use the Format function, be certain to test to see if your usage exercises the bugs . Some examples of incorrect behavior are demonstrated in Listing 6 . Scientific notation cannot handle negative exponents, dates are not formatted, currency symbols are always on the right, and the escape character (\) does not work. Table 6 contains a list of currently unsupported format specifiers.

Bug

Table 6: Unsupported format specifiers.
Code	Description
q	The quarter of the year (1 through 4).
y	The day in the year (1 through 365).
yy	Two-digit year.
yy	Complete four-digit year.
m	Month number with no leading zero.
mm	Two-digit month number; leading zeros are added as required.
mmm	Month name abbreviated to three letters .
mmmm	Full month name as text.
d	Day of the month with no leading zero.
dd	Day of the month; leading zeros are added as required.
ddd	Day as text abbreviated to three letters (Sun, Mon, Tue, Wed, Thu, Fri, Sat).
dddd	Day as text (Sunday through Saturday).
ddddd	Full date in a short date format.
dddddd	Full date in a long format.
w	Day of the week as returned by WeekDay (1 through 7).
ww	Week in the year (1 though 52).
h	Hour with no leading zero.
hh	Two-digit hour; leading zeros are added as required.
n	Minute with no leading zero.
nn	Two-digit minute; leading zeros are added as required.
s	Second with no leading zero.
ss	Two-digit second; leading zeros are added as required.
ttttt	Display complete time in a long time format.
c	Display a complete date and time.
/	Date separator. A locale-specific value is used.
:	Time separator. A locale-specific value is used.
@	Character placeholder. If the input character is empty, place a space in the output string. For example, "(@@@)" formats to "( )" with an empty string.
&	Character placeholder. If the input character is empty, place nothing in the output string. For example, "(&&&)" formats to "()" with an empty string.
<	String in lowercase.
>	String in uppercase.
!	Normally, character placeholders are filled right to left; the ! forces the placeholders to be filled left to right.

Listing 6: ExampleFormat is found in the String module in this chapter's source code files as SC06.sxw.

 Sub ExampleFormat   MsgBox Format(6328.2, "##,##0.00")            REM = 6,328.20   MsgBox Format(123456789.5555, "##,##0.00")    REM = 123,456,789.56   MsgBox Format(0.555, ".##")                   REM .56   MsgBox Format(123.555, "#.##")                REM 123.56   MsgBox Format(123.555, ".##")                 REM .12356 (broken)   MsgBox Format(0.555, "0.##")                  REM 0.56   MsgBox Format(0.1255555, "%#.##")             REM %12.56   MsgBox Format(123.45678, "##E-####")          REM 12E1   MsgBox Format(.0012345678, "O.OE-####")       REM 1.2E3 (broken)   MsgBox Format(123.45678, "#.e-###")           REM 1e2   MsgBox Format(.0012345678, "#.e-###")         REM 1e3 (broken)   MsgBox Format(123.456789, "#.## is ###")      REM 123.45 is 679 (strange)   MsgBox Format(8123.456789, "General Number")  REM 8123.456789   MsgBox Format(8123.456789, "Fixed")           REM 8123.46   MsgBox Format(8123.456789, "Currency")        REM 8,123.46$ (broken)   MsgBox Format(8123.456789, "Standard")        REM 8,123.46   MsgBox Format(8123.456789, "Scientific")      REM 8.12E03   MsgBox Format(0.00123456789, "Scientific")    REM 1.23E03 (broken)   MsgBox Format(0.00123456789, "Percent")       REM 0.12% End Sub

The supported format specifiers are shown in Table 5. The documentation indicates that other format specifiers are supported, but they are not (see Table 6 ). Unfortunately, even some of the supported format specifiers are buggy . In my experience, simple formatting works for numbers that are not in scientific notation. For almost anything else, you'd better try it and assume that it won't work as expected unless you explicitly confirm otherwise.

Compatibility

Visual Basic supports all of the OOo Basic format specifiers, and more.