[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1290
  • Last Modified:

Numeric varchar - Export to Excel

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.

01
02
03
34
56

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

1
2
3
34
56

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.
0
expertsoul
Asked:
expertsoul
  • 2
2 Solutions
 
Saurabh Singh TeotiaCommented:
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 text..so 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 a1..you can use this...
=text(a1,"00")
Saurabh...
0
 
expertsoulAuthor Commented:
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.
0
 
expertsoulAuthor Commented:
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.
<style>
.Text {mso-number-format:\@}
</style>

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
3
mso-number-format:0% No percent
mso-number-format:"0\.E+00"; Fractions
mso-number-format:"\@" Text

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now