i have exported some(57k references from Access to Excel.

now because some fields are in text format,...,excel is puting ' sign in front of the reference.

Instead of nicely:

CN123

is

'CN123

now, as I read this sign is there because Lotus use it for left allignment.There is something in the /tools/options/transiton ...I should clear the "transition navigation keys" ... but it was cleared and didn't work

1.) I tried to replace it with Nothing but the find/replace function doesn't find the sign. I copy/paste the sign and didn't work.

2.) Second i tried with the Left function and it returns me the first sign of an reference and not the ' sign.....

But if I click in the cell I can delete it....



...maybe I was thinking in the wrong direction...

Just when I was writing the text came on my mind that I can:

Select all
copy
open new sheet
edit/paste special
values

and it WOOOOOOOORKKSSSSSSSSSSSSSSS.......................

no way...


PS: ....if sb will have some similiar problem...

Hi,

There are two ways (and a few others that some have mentioned about a month back).

One way is via a formula...

In the same row that the data is in type into an EMPTY COLUMN this...

=Value(A1)

I'm assuming that A1 is the first row of your data with the tick marks.
Copy this formula down to the last row of your data. After everything calculates (if it takes long at all). Copy and paste special values back into your original column.

The other way I know is via VBA...

Insert a new module in the VB editor and type in the following code...

(this code assumes that the tick marked data is in column A and starts in row 1)
(this code also assumes that the column with the tick marks does not have any blank fields. Meaning, that every row has data in it, if it hits a blank cell, it will stop, so you might have to nest change the last line to (Loop until activecell.offset(0,1) = ""), change the offset to however many columns to the right of the activecell that there is a column that always has data (or negative to the left))

Sub TickRemover()

Range("A1").Select

Do
Activecell = Activecell.Value
Activecell.Offset(1,0).select
Loop until activecell = ""

End Sub

Have Fun!
-Mike