custom number format

[cac]

Scientific notation To display numbers in scientific format, use "E-,"
"E+," "e-," or "e+" exponent codes in a section. If a format contains a zero
(0) or number sign(# ) to the right of an exponent code, Excel displays the
number in scientific format and inserts an "E" or "e". The number of zeros or
number signs to the right of a code determines the number of digits in the
exponent. "E-" or "e-" places a minus sign by negative exponents. "E+" or
"e+" places a minus sign by negative exponents and a plus sign by positive
exponents.
----------

This is from the help file "Create or delete a custom number format"
However, when I use e+/e- (instead of E+/E-) to define the format and click
"ok", it said that "microsoft office excel cannot use the number format you
typed". I don't know what's wrong here?

Another question is that when I specify the integer part digits, say, I
define a format "0.#E+#" for 3012.1, the display is 3.E+3, and if I define as
"00.#E+#", the display is 30.1E+2, and if "000.#E+#", the display is 003.E3
I don't know how does excel work here...

 

[David McRitchie]

It is a lot easier to provide a solution based on what you need or want
than how something works. At first it looks like you give no examples of
what you entered but I see they are buried within paragraphs, Please try
to separate things into paragraphs and columns (Excel people like columns)
to make things more readable. See if this answers your question.

The formats are essentially the same as other formats except for the exponent.
0, indicates a mandatory placement of a digit,
#, indicates a place holder as far as spacing goes, leading zeros
will not show. I don't think trailing zeros will be held as a space.

This is your basic Scientific Notation that is generated with 2 decimal places
0.00E+00 i.e. 2.22E+8
This is a variation for Engineering Notation (exponent divisible by three)
##0.0E+0 i.e. 222.2E+6

More information in HELP, to help with number formats in help see
http://www.mvps.org/dmcritchie/excel/formula.htm

Actually your first question does not say what you entered, so can't help you
with that.

Second example is explained above, but the # immediately before the E
is useless. Without the E, the # following a period would allow you to
align numbers with various decimal places on the decimal point.

If you want to force a space before the E then use
0.00_8E+0
If you do not want to see + after the E use E-0
which will only show negative Exponent values
seems help is a bit wordy -- but it is not ambiguous.
and clearly tells you that you would see E0
not E-0 or E+0 in a display.

If you want to experiment, suggest you install GetFormula and
GetFormat from my formula.htm page, and look at your
results with various formats.