Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    53

    Unanswered: Remove leading apostrophe with VBA

    Hello all, was wondering if anyone could provide some assistance. I currently use the following code to delete leading apostrophe from my exported files.

    Sub DeleteApostrophes()
    With Worksheets("Sheet1").UsedRange
    .Value = .Value
    End With
    End Sub

    This works fine but now I have data with alphanumeric characters and this no longer works. Any suggestions? Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I haven't tested this, but it ought to work.
    Quote Originally Posted by VBAnewbie View Post
    Code:
    Sub DeleteApostrophes()
        With Worksheets("Sheet1").UsedRange
            If IsNumeric(.Value) Then .Value = .Value
        End With
    End Sub
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    53
    Thanks Pat but the worksheet didn't even twitch.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this a one off taks (Ie you have got crap data that yuou want to fix?) or is it recurrign task?


    personally I'd want to test if the cell had a leading ' using the if function

    if(left(cellref,1) = "'",mid(cellref,1),cellref)

    my excel is a little rusty so you may need to fiddle around with the above to make it work, not leasrt to find the way to define the cellref.

    what I have doen in previous times is put that expression into a column away from the actual data, then copied and paste (Special) just the values back tot he source column
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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