Excel files can contain numeric cell formatting, e.g., "#,###.00". When an Excel file is imported into the worksheet, the worksheet stores the Excel number formatting internally so that the same formats are preserved when the file is saved. The worksheet only understands a subset of the codes. When it finds a character that it does not understand, the worksheet prints the literal format character. The worksheet displays a reasonable representation of the unsupported Excel characters so the value displayed in the cell looks similar to what was displayed in Excel.
Here is a summary of all the Excel number format characters that the worksheet understands:
|
Character |
Character Value |
Definition |
|
\ |
Escape |
The character following the escape is treated as a literal character. It is not interpreted as formatting character. |
|
" |
Quote |
All characters between the first quote and the next quote are printed exactly as they appear. Quote characters themselves can printed by using the escape sequence \". |
|
# |
Digit placeholder |
If the number has more digits to the right of the decimal point than there are number of characters, the number is rounded to as many decimal places as there are number of characters. If the number being printed has more digits to the left of the decimal point than there are number of characters, all the digits are printed. |
|
0 |
Digit placeholder (Force a digit) |
This is the same as the # character except that if there are fewer digits in the number than there are 0 format characters then a 0 is printed in the same place it appears in the format string. |
|
? |
Digit or space digit place holder |
This is the same as the 0 character except that this prints a space instead of 0. In Excel, this is also used for formatting fractions but this meaning is not supported by the worksheet. |
|
. |
Decimal point (print a decimal point) |
This determines how many digits are printed to the right and left of the decimal point depending on how many digit formatting characters, # or 0, appear on either side of the decimal point in the format string. The actual character printed may not be a period (".") depending on the number format preferences set in the control panel. |
|
, |
Comma separate |
If this format character appears anywhere in a format string, except the exponent, then the number is printed with thousands separators or whatever primary digit grouping is specified in the control panel. |
|
% |
Percent |
If this format character appears anywhere in a format string, except the exponent, then the number is multiplied by 100. The percent character is printed in the same place as it appears in the format string. |
|
e+ |
Exponent |
Exponents specify scientific notation. The e+ or e- specification must be followed by one or more of the digit formatting characters (# or 0). The number of digit formatting characters after the e+ or e- determines how many exponent digits are printed. The E+ or e+ notation prints a minus sign in front of negative exponents and a plus sign in front of positive exponents. The E- or e- notation prints a minus sign in front of negative exponents. The exponent values displayed are an even multiple of the number of digit formatting characters to the left of the decimal point. For example, in the format string "##0.0e+##" there are three digit formatting characters left of the decimal point. This means that possible exponent values are e-06, e-03, e+00, e+03, e+06, etc. |
|
E+ |
Exponent |
See above |
|
e- |
Exponent |
See above |
|
E- |
Exponent |
See above |
|
$ |
Currency |
This character is replaced with the local currency character. |
|
_ |
Variable width |
The underscore and the character that follows it are replaced with a space. In Excel, the space is the same width as the character after the underscore. |
|
* |
Repeat |
The character after the asterisk is printed. In Excel the character after the asterisk is printed enough times to fill the cell width. |
|
; |
Terminator |
Marks the end of the format string. Nothing after the semicolon is used for formatting. In Excel, a format string can have several parts separated by semi-colons: "positive fmt;negative fmt;zero fmt;text fmt." |
|
[ color ] |
Specifies the color to print the number |
One of the recognized color names must appear between the square brackets. It is invalid to use square bracket anywhere else in the format string. Recognized color names are BLACK, WHITE, RED, GREEN, BLUE, YELLOW, MAGENTA, and CYAN. |
|
m, d, h, etc |
Date/time formatting |
The worksheet recognizes standard date/time formatting. Custom date/time formatting are read as strings, not as dates and times. |
Characters Not Currently Supported
All other characters are printed exactly as they appear in the format string. Excel format characters not currently supported include:
· ; Excel allows several different formats per cell depending on whether the number is positive, negative, or zero. The worksheet only uses the positive portion of the Excel format string.
· [condition] Excel allows numbers to be formatted according to their value, e.g., if the number is greater that 100 use one format, otherwise use a different format. The worksheet does not support these options.
· @ Text place holder (text in the cell replaces this character)
See Also