Removing single quotes when importing Access to Excel

[everest]

Hi,

I have a spreadsheet that I've imported from Access to Excel97. In the excel spreadsheet, I keep getting these single quotes in every cell. I've tried running a replace to delete these single quotes, but it doesn't work. Does anyone know of a way to get rid of these single quotes? Thanks.

Everest

 

[carrr]

How are you doing this? Are you using Excel's 'get external data' functionality? Are you saving from Access in some format and then opening using Excel? Details, please...

 

[everest]

Well, I'm using Access 2000, and on the screen/window that lists all the tables you have in the database, I right click on the table that I want exported to Excel97 and I click on "export". Then I just save it as an excel file. I hope this is detailed enough. Any help would be appreciated. Thanks.

Everest

 

[carrr]

Good enough. What, by chance, do you have selcted for the data type for the columns in your excel tables?

 

[everest]

I'm guessing that data type means the format of the cells? I have all of the cells formatted to General. This single quote appears. Please correct me if I'm wrong.

After playing around with it, excel seems to ignore these single quotes when performing calculations which was my biggest worry. Also, there doesn't seem to be any problem with importing it back to access.

It would be nice to know how to get rid of these single quotes, but not absolutely critical. If you do know how to get rid of these quotes, that would be great, if not, no big deal. Either way, thank you for your help.

Everest

 

[carrr]

I'm sorry....I should have asked for the data type in Your Access tables....that's where my interest lies.

 

[everest]

All of the fields are text data types. I noticed that the numeric ones don't have single quotes.

 

[xlbo]

I reckon, in excel, you have Tools>Options>Transistion Transistion Navigation Keys ticked

This option is for old Lotus users and one of the results is that if a cell has text in it (as opposed to numbers) it will have a ' in front of it
This cannot be picked up by find / replace or by formulae (ie incredibly annoying)
If you untick the option, the ' should go away Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde