Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    errant apostrophe (xp sp1)

    I often export records from access into an excel spreadsheet. It usually works fine. However, today, apostrophes appeared as the first character in each text field. I'm very confused as to what's happening. I tried writing a vba function to get rid of the apostrophes and the code didn't "see" the apostrophe.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: errant apostrophe (xp sp1)

    The apostrophe is a "text indicator": it marks that the contents are "explicitly defined" as text. If you add it before something that looks like a number or a date or a hyperlink, for example, the contents will be text instead of letting excel decide based on its routines.

    This macro will remove them. Select the cells and run the macro:

    Steve
    <pre>Sub RemoveApostrophe()
    Dim rCell As Range
    For Each rCell In Selection
    rCell.Value = rCell.Value
    Next
    End Sub</pre>


  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: errant apostrophe (xp sp1)

    As Steve pointed out, the apostrophies indicate that the cell contents are to regarded as text, but are not treated as part of the cell value by Excel. If you do not need to evaluate the cells as numbers you could just ignore the apostrophies. Otherwise you can use Steve's macro, or without resorting to VBA, use the steps outlined in <post#=322287>post 322287</post#>.

    Andrew C

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: errant apostrophe (xp sp1)

    Andrew,
    Yours might be a better solution than mine in some cases.
    The macro I proposed will essentially "paste-special - values" if there are any formulas present.

    Your method will still keep the formulas and just tack on the multiplication after enclosing in parentheses:
    =(originalFormula)*1
    Steve

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: errant apostrophe (xp sp1)

    Thanks a lot folks. Now I understand why I couldn't format my zip code values! I appreciate your help.

Posting Permissions

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