Using the Accounting Format

You can use an accounting format on cells. The accounting format allows you to align the currency symbols and decimal points for numbers in a column. Negative numbers are displayed in parentheses and zeros are displayed as dashes.

The following table lists the accounting formatting characters and the formatting characters that have been enhanced for the accounting format:

Character	Description
_	You can use the underscore character "_" to create a space that is the width of a character in a number format. For example, _) causes positive numbers to line up with negative numbers that are enclosed in parentheses.
*	Use the asterisk "" in the number format to repeat the next character so that it fills the column width. For example, type 0- to include enough dashes after a number to fill the cell. Type *0 before any format to include leading zeros.
,	The comma displays the thousands separator in a number. Spread separates thousands by commas if the format contains a comma that is enclosed by number signs "#" or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number $12200.0 is displayed.
%	Use the percent sign "%" in the number format to display numbers as a percentage of 100. For example, to display .08 as 8%.
?	This digit placeholder follows the same rules as 0 (zero); however, Spread adds a space for insignificant zeros on either side of the decimal point.

The following list contains limitations when using formatting:

If word wrap is allowed and the cell contains multiple lines, then the repeat characters do not take effect.
Repeat characters are ignored if the cell supports text overflow.
The repeat characters are not used with automatic fit.
The repeat character does not become blank with shrink to fit.
The blank area does not display a text decoration.

For additional information about formatting cells, refer to Formatting Cells.

Using Code

This example sets the accounting format for cells.

JavaScript	Copy Code
activeSheet.setValue(1, 0, 'Standard Accounting Format : _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'); activeSheet.setValue(2, 0, 'Value'); activeSheet.setValue(3, 0, 12); activeSheet.setValue(4, 0, -12); activeSheet.setValue(5, 0, 0); activeSheet.setValue(6, 0, 'Text'); activeSheet.setValue(2, 2, 'Formatted Result'); activeSheet.getRange(3, 2, 4, 1).formatter('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'); activeSheet.setValue(3, 2, 12); activeSheet.setValue(4, 2, -12); activeSheet.setValue(5, 2, 0); activeSheet.setValue(6, 2, 'Text');

JavaScript

Copy Code

activeSheet.setValue(1, 0, 'Standard Accounting Format : _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)');
activeSheet.setValue(2, 0, 'Value');
activeSheet.setValue(3, 0, 12);
activeSheet.setValue(4, 0, -12);
activeSheet.setValue(5, 0, 0);
activeSheet.setValue(6, 0, 'Text');
activeSheet.setValue(2, 2, 'Formatted Result');
activeSheet.getRange(3, 2, 4, 1).formatter('_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)');
activeSheet.setValue(3, 2, 12);
activeSheet.setValue(4, 2, -12);
activeSheet.setValue(5, 2, 0);
activeSheet.setValue(6, 2, 'Text');

This example uses the setFormatter method.

JavaScript	Copy Code
activeSheet.setValue(1, 2, 12.34); activeSheet.setFormatter(1, 2, '$* #.##');