Solved

Coldfusion Report Builder - Excel removes leading zeros

Posted on 2007-03-29
3
725 Views
Last Modified: 2013-12-20
Using ColdFusion Report Builder, I am producing an excel report. The excel report is stripping out leading zeros from a text field. For instance, the data looks like this: 000006456 When it is put into excel, it looks like this: 6456
0
Comment
Question by:Nigel-SA
  • 2
3 Comments
 
LVL 1

Expert Comment

by:sagergaard
ID: 18815109
This is because Excel tries to help, and says "This could be a number. I'll make it a number."
Make sure the columns format is set to Text.  (rightclick on the column and choose Format cells.
0
 
LVL 1

Accepted Solution

by:
sagergaard earned 500 total points
ID: 18815122
Another possibility could be to use a ' in front of the 000006456. This way Excel is forced to display the whole "number". And it still hides the '.
0
 

Author Comment

by:Nigel-SA
ID: 18816032
Thanks you gave us an idea, in ColdFusion Report Builder use the statement below to kick a ' before the number so excel sees it as a text field.

Insert("'", query.FIELD, 0)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

910 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

20 Experts available now in Live!

Get 1:1 Help Now