100 Comments

← Previous Page 2 of 2
  1. carl

    Nice! It worked beautifully for me with a date field. I was totally stumped and you’ve saved me a ton of time. Thank you!

  2. teum

    @Piotr

    This solution worked great for me. Thanks a lot!
    But don’t forget in Data -> Text to Columns to to check “other” as separator and to type ” ‘ ” (without the quotes) just next to this option.

  3. Mike

    The text to columns suggestion worked and got rid of the apostrophes that were changing my numbers into text. However, after saving the file and reopening it, the apostrophes are back. Does anyone know if there’s a default setting that is causing this?

  4. FLOG51

    Thanks, first fix I have found that is immediate. A lot of the other Formatting suggestions have not worked.

  5. BigBob

    I’ve wasted several hours trying to figure this out in calc. I finally googled the problem and you solution popped. It works. Thank you!

  6. cmbmcn

    Mike :
    The text to columns suggestion worked and got rid of the apostrophes that were changing my numbers into text. However, after saving the file and reopening it, the apostrophes are back. Does anyone know if there’s a default setting that is causing this?

    Mike I have only had this problem with date columns imported using csv files. This solution works for me. Highlight the entire column with the offending invisible apostrophe;
    click on Data > Text to Columns > click OK
    in the resulting dialog box. I have never changed the options in the Text to Columns dialog box but the apostrophes disappear. I then can format the dates as needed.

    One further error I get is if the Default language is set to English(USA), I can’t format the entire column of dates as yyyy/mm/dd for import into the database unless I change the default language to English(UK).

  7. Deb

    I realize this is an older post, but just to update (without reading all the comments to see if it’s been suggested already…sorry) this solution:
    Edit
    Find & replace
    (search for) ‘
    (replace with) leave blank
    Then I just clicked “replace all” and closed.
    Problem solved.

    Thank you for directing me to the right area and saving me tons of time by not needing to correct them individually…. (aargh..)!

  8. Natalie Bjorklund

    Thank you! Don’t know how you figured it out but I have been saved many hours of work thanks to your posting this. Well done!

  9. Hi Ryan,
    Thanks for this. Every year when I do my taxes and download the csv from paypal I always have this problem and always find your website with the solution. If there was a facebook like button I would click on it. It gets the thumbs up from me. Thank you.

  10. For a long time have I put up with this issue and performed the long winded approach of removing each apostrophe one at a time – but I shall do it no more thanks to you! :)

  11. Gregg

    You found this way back in 2009 and it’s still of use. Saved me a ton of time–thanks for sharing!

  12. Jelle

    Even after four years, I am still able to find and use this great workaround. Thanks, saved me bacon ;-)

  13. Perfect, exactly what I was search for. It’s very important if you work with HLOOKUP (DE = SVERWEIS) and you’re getting #NV results. Mostly found with XLS files delivered from 3rd party sources.

  14. Sindy

    Thank you!!!!!!!! I cannot thank you enough.Wow. You just saved me hours and hours of aggravation.

  15. Judijo

    It took me over a week to find the problem (the apostrophe) and I finally came upon the solution via you! Thank you SO much. Now my accountant will love me again. :)

    Nice to see a 5 year old blog is still helping others! You do BOMB! <3

  16. Thanks buddy. This worked for me.
    Sometimes, it really is worthwhile paying Microsoft for their Office suite.
    I do have the licensed version of MS office, but its on my Laptop with windows and my simulations are in linux with lots of columns (~60-100) and lots of rows in each column (~10000) Libreoffice at least takes my data without whining on memory

    I guess we cant have it bothways. Ohh well…

    Anyways, thanks for this.
    May God Bless you. Wish I could share a cup of coffee with you :)

    Best,

Leave a Reply

Your email address will not be published.