Is there a setting to allow leading ad trailing zeros to be shown?

We have an external program that creates CSV files for use in Excel that works beautifully, however when cells in the CSV file have eading or trailing zeros and we pull that file into Excel, the leading or trailing zeros do not show up. I am aware that I can use Excel's formating to change the cells in question to display leading or trailing zeros, however, that is cumbersome when dealing with several hundred files in the course of a week.

Is there a setting in Excel that can be turned on or off in order to facilitate the display of leading or trailing zeros? I have looked in Goodl, but so far have not been successful. I also checked out Microsoft's site, but again, no luck.

Any ideas and suggestions are most welcome. Thank you.

Hi, benishiro and thanks for replying so quickly!! And, for your suggestion. Question for you... will that suggestion "stick" to Excel, so that I don't have to do it for each csv file I pull into Excel? I am looing andhoping for an Excel 2010 option or setting that willl force Excel to always accept leading or trailing zeros when my Excel brings in a csv file with leading or trailing zeros.

Thanks again!

Here's something I've used. I assume it will still work the same in the newer versions:

1) Import the text file into Excel using the Import External Data command (not sure where it will be on the ribbon, but I know it is still there. This should bring up the text import wizard.
2) If Excel doesn't recognize it automatically, specify a delimited file with comma as the delimiter.
3) As benishiryo suggested, at the appropriate step, be sure to specify that this field is to be imported as text.
4) At the final step, be sure to tell Excel to "save query definition". At this point you have set up a "query table" that will allow Excel to import the text file into the spreadsheet and remember what it did when importing that file.

I'm not sure exactly what will be the most efficient way to handle all the files you are processing. One possibility: you could set the "prompt for file name on refresh" property of the data range to yes. Then, each time you call the "refresh data" command, it will prompt you for the file name, and you could work through the files on at a time. Each refresh will import the files using the same properties as you originally indicated.

You might try that to see if it helps.

Benishiryo and MrShorty, thanks for your replies, they are appreciated. I think I'll just end up advising our users that they will have to format their columns that need to have leading or trailing zeros. Wouldn't it be nice if Microsoft had an option of turning leading/trailing zeros on or off?