Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Apostrophe's everywhere

    I have name and address data imported from Outlook in an Excel 2010 data sheet. For some reason there is an apostrophe (') at the beginning of each field. The apostrophe doesn't show up in any of the Excel cells, but it shows up in the formula bar when a cell is selected. I've tried replacing all apostrophes with nothing, but they remain in place. Any ideas on how to get rid of these or why they are there in the first place?

  2. #2
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,420
    Thanks
    33
    Thanked 195 Times in 175 Posts
    Putting an apostrophe in front of a numeric field makes it appear as text to Excel...

    For example, this enables you to enter '001376 and have it appear in the cell as 001376, not transformed to become the numeric value 1376. Sometimes you actually want to display leading zeroes!
    BATcher

    Time prevents everything happening all at once...

  3. #3
    4 Star Lounger
    Join Date
    Jun 2011
    Location
    Hampshire (the old one)
    Posts
    525
    Thanks
    21
    Thanked 72 Times in 62 Posts
    I've been trying to get rid of useless apostrophes for year's. Oops, there's another one

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Chuck,

    If you copy the cells in Excel and then Paste Special Values right back into the same cells it will get rid of the apostrophies.
    Also Exporting the file from Excel to a .CSV and then reloading the .CSV seems to get rid of them also.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks to everyone. I now have a solution to my problem.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, clearing formats should also remove the apostrophes.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Plenty of good suggestions above. I found in older versions of Excel that cells formatted as text were given a "hidden" apostrophe at the beginning of the cell that did not show up in the formula bar. On formatting as a number that hidden apostrophe remained and the cell continued to behave as text. Nevertheless, editing the cell in the formula bar, putting the cursor at the left edge and hitting backspace DID delete the apostrophe and all returned to sanity.

    I believe you can trace that apostrophe all the way back to Visicalc. It was certainly a common feature of Lotus 123. These days it should be consigned to the dustbin of history.

    Incidentally, trivial point in an Excel discussion but I think it is good practice in data handling to either replace all apostrophes with another character like "`" (from the key to the left of the number row) or remove them. If you ever feed your data into SQL Server or another database apostrophe is a text delimiter and can trigger disaster. For a humorous reason why, see http://xkcd.com/327/

Posting Permissions

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