Leading zeroes to Excel

[AndyBannister]

I am running a report that produces a field containing ISBN numbers, a 10 digit code which often consists of a leading zero. I have no problem at all handling the leading zero in Access. However, the problem comes when exporting that report to Excel. My users normally just use the button on the toolbar to do this --- and lo and behold, Excel strips off the leading zero. I have tried everything I can think of to stop this to no avail. Can anybody offer any advice or suggestions?

Many thanks.

Andy Bannister

 

[llkhoutx]

One solution ois to convert the number, with leading zeroes, to text and then export.

 

[AndyBannister]

I've tried that and it doesn't seem to work. When the text field is brought into Excel, Excel treats it as a numeric field and strips off the zero.

 

[simongallop]

What happens if when in Excel you reformat the field to text. Do the zeros reappear?

 

[AndyBannister]

Hi, Harry,

No --- Excel assigns the imported field a type of "general". Turning this back to "text" makes no difference; it seems that during the import Excel decides this field is numeric and 'helpfully removes' the zero.

Thanks,

Andy

 

[simongallop]

How about forcing an apostrophe in front of the string as then Excel will read the string as text? ie MyField: "'" & ISBN_Num

 

[simongallop]

The other way is to format the column 0000000000 in Excel after the import (presuming that your field is always 10 digits)

 

[AndyBannister]

Harry,

Thanks. I'd hit upon the idea of sticking an apostrophe (or space) in front of the field before, but wanted to avoid the user then having to do a search and replace in Excel to remove them all. The format idea in Excel is perhaps a better solution. But it's still annoying that Access generates this problem in the first place ...

Thanks for your advice and help.

Cheers,

Andy