I'm using a web server to create an excel file on the fly using table output. I then click on a link to save the file on my harddrive, normal web browser behavior.
This file has domestic and international addresses and excels default behavior is to strip leading zeros. I have tried to find an option to turn this off. Sure it is easy enough to handle domestic zip codes, but the internationals are a different story.
I tried creating templates and they work whenever I open excel, but this file will not use those templates as it is already an excel file. How can I turn off this 'general formatting' feature?
Thanks!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Is it a csv file? Open the file in notepad and you'll see your leading zeros are still there.
Open Excel, click Data - Import External Data - Import Data and browse to your csv file. As you go thru the import wizard change the column type to Text for the column with leading zeros and they'll come into Excel that way.
Hope that helps.
Scot
What I'm doing is setting the content of the webpage to be excel, therefore, when someone browses to that webpage, instead of getting HTML, the browser asks them to open or save an excel file. The content of that webpage is a table with rows which excel understands.
Therefore, excel cannot be open before the file is opened and I need to force excel to stop stripping leading zeros.
I even changed my zip codes to have a ^ in front of them so that excel automatically treated them as text. I opened the spreadsheet changed all the cells to be of text format and then did a mass find and replace of ^. Do you know what happened? Excel stripped all the leading zeros with the ^ and left the cell format as text.
What gives?
I'm not an expert in HTML by any means, but I think this article may provide what you are looking for: http://www.agoric.com/sour
HTH
Rory
Business Accounts
Answer for Membership
by: roryaPosted on 2006-12-08 at 08:05:07ID: 18102290
Hi,
You need to either format the cells as Text before assigning values to them, or assign a value prepended with a single quote - Excel will then treat the number as text and retain the leading zero.
HTH
Rory