Excel: Leading quote in cells when export from Access

[norty303]

I've exported a table to Excel from Access and noticed that when I select a cell it shows the value with a leading ' (single quote) when viewed in the formula bar. This does not show in the actual cell.

Does anyone know why this occurs, if it's possible to turn it off (find/replace fails to find the character) and whether it will recognize the character if we try to perform matching functions in Oracle?

Thanks

 

[xlbo]

check Tools>Options>Transistion

Make sure "Transistion Navigation Keys" is unchecked

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[norty303]

Yep, it's unchecked, although can't see why that would insert a character into the cells.

 

[xlbo]

No - neither can I but it does - something to do with old Lotus123 users !

It is there to indicate text and it can certainly cause some issues when trying to do matches.

Are any of the boxes ticked on the "Transition" tab

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[norty303]

Ahhhh actually I think it may be that the export from Access may have been setup to be 123 compatible hence the writing of the leading '

Nothing is ticked in the transitions tab at all but I think this is probably to do with the source of the data rather than something Exel is doing to it. It's just a little funny that Excel can't find the characters when performing a search.

 

[xlbo]

As I said, it is there to indicate text - it is not recognised as a character in its own right - won't even be counted using the LEN() function

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[norty303]

Thanks Geoff, my only concern now then is whether Oracle knows what to do with it although it may be easier to write some SQL to remove the leading ' if it exists before writing to the table.

Cheers for your help

 

[xlbo]

I concur - I would've thought it would be better to strip the ' out before it goes into your Oracle table - although you could just do a trial import into a dummy table and see what it does !

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[OrlandoG7]

This would involve 2 steps but it seems to drop the '

=TRIM(B1)

Then copy and paste values

Orlando
Data Management Services and Custom Programming Services

 

[rss01]

I have the same issue. I always just SAVEAS a text(tab delimited) file. Then I open that file up and SAVEAS a excel file.