Exporting Access Table into Excel Number Difficulty

[laserluv]

I am working with a data file in Access that is giving me difficulties when I try to export the file. There is a column, imported as text that has both alpha and numeric characters.

Example:
456879E5651

When it exports to excel, it gets transposed to scientific notation
456879+001E (etc...)

How can I export my 18000 row table to recognize the column as true text? The file was originally imported as a CSV file, and can be queried by using the actual invoice number, with the mixed characters.

Any help would be appreciated!

LL

 

[ChrisUK]

How are you doing the export ?

Easy way if you are able is to add a single quote (') to the start of your column, this tells excel to treat the value as text - the quote itself is not displayed.

HTH

Chris

 

[laserluv]

How do you recommend I add the single quote? I have tried to do it after it is exported to Excel, but it leaves the column in Scientific notation. Do I need to create a query in Access to accommodate, and add the apostrophe?

Thanks-
LL

 

[Royal Presence]

I have this problem often.

Prior to the newer versions of Office, one had to make an excel file before exporting and format the conditions of the cells as wanted.

I have the opposite problem in which I want to remove the ' apostrophe, which I just found on thread. But for your instance, what happens when you select and format the column as text prior?

 

[mdmilner]

Going to assume you're probably using:

DoCmd.TransferSpreadsheet

I'd suggest taking the SQL approach.

UPDATE tblName Set field1="'" & [field1]
WHERE Left([field1],1)<>"'"

Simple check - if the first character of the field is already an ' then don't do it, if it isn't, then add it.

Mike