3 Replies Latest reply on Dec 21, 2006 1:06 PM by gr8white

    Excel thinks it's a date

    gr8white
      I'm outputting address info to Excel using cfcontent, where each address element is a separate cell ("123","MAIN", "ST"). Believe it or not there is a street called "1/2 Road", and Excel thinks "1/2" is a date (2-Jan) and displays it as such. I don't think it's a matter of applying a format to the cell as entering 1/2 in an Excel cell then formatting it as text returns something other than "1/2". If I enter '1/2 into an Excel cell it recognizes it as text but doing that in CF actually includes the ' as part of the string. The only workaround I've come up with is preceding the name with a space but I don't really want to have extra spaces that don't belong there.

      Thanks,
      Ken
        • 1. Re: Excel thinks it's a date
          gr8white Level 1
          I experimented and found that applying a text format to the cell did solve the problem:
          <td style="mso-number-format:\@; ">

          Thanks,
          Ken
          • 2. Re: Excel thinks it's a date
            Level 7
            What about quotes on both sides?

            Are you generating a CSV or an XSL file?


            gr8white wrote:
            > I'm outputting address info to Excel using cfcontent, where each address
            > element is a separate cell ("123","MAIN", "ST"). Believe it or not there is a
            > street called "1/2 Road", and Excel thinks "1/2" is a date (2-Jan) and displays
            > it as such. I don't think it's a matter of applying a format to the cell as
            > entering 1/2 in an Excel cell then formatting it as text returns something
            > other than "1/2". If I enter '1/2 into an Excel cell it recognizes it as text
            > but doing that in CF actually includes the ' as part of the string. The only
            > workaround I've come up with is preceding the name with a space but I don't
            > really want to have extra spaces that don't belong there.
            >
            > Thanks,
            > Ken
            >

            --

            Don Vaillancourt
            Director of Software Development
            WEB IMPACT INC.
            phone: 416-815-2000 ext. 245
            fax: 416-815-2001
            toll free: 866-319-1573 ext. 245
            email: donv@web-impact.com <mailto:donv@webimpact.com>
            blackberry: donv@rogers.blackberry.net <mailto:donv@rogers.blackberry.net>
            web: http://www.web-impact.com
            address: http://maps.google.com
            < http://maps.google.com/maps?f=q&hl=en&q=99+atlantic+ave,+toronto&ie=UTF8&z=15&ll=43.640765 ,-79.420767&spn=0.013448,0.04343&om=1&iwloc=addr>


            This email message is intended only for the addressee(s) and contains
            information that may be confidential and/or copyright.

            If you are not the intended recipient please notify the sender by reply
            email and immediately delete this email.

            Use, disclosure or reproduction of this email by anyone other than the
            intended recipient(s) is strictly prohibited. No representation is made
            that this email or any attachments are free of viruses. Virus scanning
            is recommended and is the responsibility of the recipient.
            • 3. Re: Excel thinks it's a date
              gr8white Level 1
              I'm using cfcontent to generate Excel output inline - it's .XLS rather than .CSV.

              If I use "1/2" it actually shows the quotes in the Excel cell which isn't what I want.

              But as I said the css style did solve the problem.

              Thanks for the input.

              Ken