leading zero's for excel content

[yanir]

Hi
I use reponse.contenttype = "application/vnd.ms-excel"
So the browser will show the data in excel format, but for
some fields I use leading zero's, which truncated by the
browser, at this content type.
If I concat a "'", or other none numeric char, the problem
is solved inconsistently!!!
But even this solution is problematic - is there any
solutions, explanation?

 

[Aaron Bertrand - MVP]

If Excel sees a number, it's going to drop leading zeros, because they're
meaningless. If you are trying to represent a number as a string, then
treat it as a string (prefix with ' )... why is this problem "solved
inconsistently" or "problematic"?

 

[Dave Anderson]

I use reponse.contenttype = "application/vnd.ms-excel"
So the browser will show the data in excel format, but for
some fields I use leading zero's, which truncated by the
browser, at this content type.
If I concat a "'", or other none numeric char, the problem
is solved inconsistently!!!
But even this solution is problematic - is there any
solutions, explanation?

One solution is to put a non-breaking space (&nbsp in the cell. This
instructs Excel to treat the data as text. You can also use specific style
attributes that Excel recognizes, such as mso-number-format. This example
imposes a 5-digit mask on the numeric cell contents:

<TD STYLE="mso-number-format:00000">

I have no idea where this is documented. I reverse-engineered by saving an
Excel spreadsheet as HTML and reading source code.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[yanir]

You helped me a lot, thanks

-----Original Message-----
I've found when you use the ' like this, you'll wind up seeing the ' in the
cell - pretty irritating. You may be able to do:

<td>=TEXT(<%=YourNumber%>,"<%=String(Len