Numeric varchar - Export to Excel

Posted on 2009-02-20
Last Modified: 2012-05-06
Hi Experts

I am trying to export some data which is in numeric format with 0 as prefix to few of the values as below.


When I export this data to excel, it is changed to


How do I keep '0' in place. Currently I am prefixing this data with a quote, but I guess there should be a better solution.
For export I am creating an HTML and exporting it as xls.

Let me know if any more info is required.
Question by:expertsoul
    LVL 59

    Assisted Solution

    by:Saurabh Singh Teotia
    Well two ways...
    first..format the cells where you are exporting to text and it will take care of the same since excel recognises number and anything in front of it which is a zero sign changes it to change it to thte text format before exporting...
    second...lets say if you want to have two characters in your data....instead of 1..then you have data in can use this...
    LVL 12

    Author Comment

    1. I am currently formatting it using a quote but actually that is also incorrect. If someone wants to load that excel directly that quote will be an issue.

    2. I am not sure if I can use any formula while export html to excel.
    LVL 12

    Accepted Solution

    Found the solution :). For excel you have following formats available that can be specified in css file with HTML being exported.
    So in my case this one worked.
    .Text {mso-number-format:\@}

    mso-number-format:0 NO Decimals
    mso-number-format:"0\.000" 3 Decimals
    mso-number-format:"\#\,\#\#0\.000" Comma w\3 dec
    mso-number-format:"mm\/dd\/yy" Date7
    mso-number-format:"mmmm\\ d\\\,\\ yyyy" Date9
    mso-number-format:"m\/d\/yy\\ h\:mm\\ AM\/PM" D -T AMPM
    mso-number-format:"Medium Date" 01-mar-98
    mso-number-format:"d\\-mmm\\-yyyy" 01-mar-1998
    mso-number-format:"Short Time" 5:16
    mso-number-format:"Medium Time" 5:16 am
    mso-number-format:"Long Time" 5:16:21:00
    mso-number-format:Percent; Percent
    SUGI 28 Advanced Tutorials
    mso-number-format:0% No percent
    mso-number-format:"0\.E+00"; Fractions
    mso-number-format:"\@" Text

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    Excel file corrupted. 13 27
    report c# 9 55
    Please explain "Promise Pattern" and how it's used 2 16
    Azure Web App - See files 3 8
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now