Special Excel Characters

Excel files can contain numeric cell formatting (codes like "#,###.00", for example). When an Excel file is imported, 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.

.

Prints 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 will be 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.

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 will only use the positive portion of the Excel format string.

  • [condition] Excel allows numbers to be formatted according to their value (i.e. 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)

  • m Month or minute

  • mm Month or minute

  • mmm Month

  • mmmm Month

  • d Day of month

  • dd Day of month

  • ddd Day of week

  • dddd Day of week

  • h Hour

  • hh Hour

  • AM/PM Displays times in AM PM format instead of 24 hour format

  • am/pm

  • A/P

  • a/p

The import filter converts most of the cells with a time and date format into appropriately formatted strings. The cell appearance is similar, although math functions are not available on the imported strings. Note: if the file is saved in Excel format, the original Excel-formatted dates will be lost.

See Also

Excel Files