I have an incompatibility between Excel 2000 and 2003 and I want to
understand why. I have the formula
=TEXT(12, "N00")
In Excel 2000 I get
N12
In Excel 2003 I get
#Value!
and Excel 2003 indicates the error as "A value in the formula is of the
wrong data type." If I change the formula to
=TEXT(12, "\N00")
Then I get
N12
for Excel 2003. Why do I need the back slash and what am I telling TEXT
about the format by adding the back slash?

Art

On 9 Nov 2006 16:51:40 -0800, "Art H" wrote:

I have an incompatibility between Excel 2000 and 2003 and I want to
understand why. I have the formula
=TEXT(12, "N00")
In Excel 2000 I get
N12
In Excel 2003 I get
#Value!
and Excel 2003 indicates the error as "A value in the formula is of the
wrong data type." If I change the formula to
=TEXT(12, "\N00")
Then I get
N12
for Excel 2003. Why do I need the back slash and what am I telling TEXT
about the format by adding the back slash?

Art

There is an official list of characters that can be used in custom format codes
that are not required to be quoted.

The following characters are displayed without the use of quotation marks:
$ Dollar sign - Negative sign
+ Plus sign / Solidus (slash)
( Left parenthesis ) Right parenthesis
: Colon ! Exclamation mark
^ Circumflex accent (caret) & Ampersand
' Apostrophe ~ Tilde
{ Left curly bracket } Right curly bracket
< Less-than sign Greater-than sign
= Equals sign Space character

Although not listed, some letters can also be displayed, but the general rule
is to enclose the text characters in double quotation marks (" ") or precede a
single character with a backslash (\).

For other than those characters above, it's always safest to quote or precede
by a backslash.

In the TEXT function, the format code needs to follow the same rules. So if
you did not want to use the backslash, you could use quotes. But to use quotes
inside quotes, you need to use double quotes.

So an equivalent formula would be:

=TEXT(12,"""N""00")

I prefer to use the backslash to quote when I just have one (or maybe two)
letters to include in this sort of formula.

I don't know why N00 worked in 2000 but you now must quote the N. Not having
2000, I cannot tell if use of the unquoted N was documented in that version. I
don't believe that the use of unquoted letters was documented in XL97 or
before, either.




--ron

Thanks for the details Ron. Just what I needed.

Art
Ron Rosenfeld wrote:
On 9 Nov 2006 16:51:40 -0800, "Art H" wrote:

I have an incompatibility between Excel 2000 and 2003 and I want to
understand why. I have the formula
=TEXT(12, "N00")
In Excel 2000 I get
N12
In Excel 2003 I get
#Value!
and Excel 2003 indicates the error as "A value in the formula is of the
wrong data type." If I change the formula to
=TEXT(12, "\N00")
Then I get
N12
for Excel 2003. Why do I need the back slash and what am I telling TEXT
about the format by adding the back slash?

Art

There is an official list of characters that can be used in custom format codes
that are not required to be quoted.

The following characters are displayed without the use of quotation marks:
$ Dollar sign - Negative sign
+ Plus sign / Solidus (slash)
( Left parenthesis ) Right parenthesis
: Colon ! Exclamation mark
^ Circumflex accent (caret) & Ampersand
' Apostrophe ~ Tilde
{ Left curly bracket } Right curly bracket
< Less-than sign Greater-than sign
= Equals sign Space character

Although not listed, some letters can also be displayed, but the general rule
is to enclose the text characters in double quotation marks (" ") or precede a
single character with a backslash (\).

For other than those characters above, it's always safest to quote or precede
by a backslash.

In the TEXT function, the format code needs to follow the same rules. So if
you did not want to use the backslash, you could use quotes. But to use quotes
inside quotes, you need to use double quotes.

So an equivalent formula would be:

=TEXT(12,"""N""00")

I prefer to use the backslash to quote when I just have one (or maybe two)
letters to include in this sort of formula.

I don't know why N00 worked in 2000 but you now must quote the N. Not having
2000, I cannot tell if use of the unquoted N was documented in that version. I
don't believe that the use of unquoted letters was documented in XL97 or
before, either.




--ron