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"?

"yanir" <ya*****@hotmail.com> wrote in message
news:04****************************@phx.gbl...

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?

[Dave Anderson]

"yanir" wrote:

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 thecell - pretty irritating. You may be able to do:

<td>=TEXT(<%=YourNumber%>,"<%=String(Len (YourNumber), "0")%>")</td>
to produce
<td>=TEXT(0001,"0000")</td>

Ray at work

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in messagenews:eJ**************@TK2MSFTNGP10.phx.gbl...

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"?

"yanir" <ya*****@hotmail.com> wrote in message
news:04****************************@phx.gbl...

> 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?
>

.