Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    Unanswered: 'sign => left(string, 1) ...

    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...
    Back to the basics...

  2. #2
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    Two ways you can remove the ' ticks.

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •