Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing apostrophes (2003)

    I have a spreadsheet with data I've exported from an Outlook calendar. All the dates and times have the dreaded apostrophe in front of them, so are acting as text. I've tried find and replace, and I've tried a bit of code (below) and neither seem to work. How do I get rid of the apostrophes so that I can carry out some calculations on the times and dates?

    The VBA I tried was:
    For Each ocell In Selection
    If Left(ocell, 1) = "'" Then ocell = Right(ocell, Len(ocell) - 1)
    Next ocell
    Waggers
    If at first you do succeed, you've probably missed something.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing apostrophes (2003)

    You could use

    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange ' or Selection
    If IsDate(oCell) And Not oCell.HasFormula Then
    oCell = DateValue(oCell)
    End If
    Next oCell

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing apostrophes (2003)

    Try theis code:

    <code>
    Public Sub FixVals()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Value = oCell.Value
    Next oCell
    End Sub
    </code>
    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks

    Thanks, that works beautifully.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  5. #5
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing apostrophes (2003)

    Thanks Hans; the dates and times are stored in separate cells; your code works well for the dates, but sets all the times to zero (or 00:00). Legare's solution does the job for both. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing apostrophes (2003)

    Legare's code will also convert numbers-as-text to numbers. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

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