Nov

29

remove apostrophes before numbers in OpenOffice Calc

Posted by : ryan | On : 11/29/2009

Number of Views: 33303

The Problem:

You have a spreadsheet that has a column of what you think are numbers in it.  The numbers display fine in their cells, but when you try to do something with them, you find that you can’t.  For example, you may want to sort them or use them as part of a calculation and it doesn’t work.  Here is a screenshot illustrating that the numbers look like numbers in their cells:

apostrophe problem - 01

If you click on one of the cells, you’ll see that the numbers actually have a hidden apostrophe in front of them, like you see in this screenshot:

apostrophe problem - 02

After spending some time on the internet, I finally found out what is going on.  While you think those are numbers, the apostrophe is Calc’s way of saying that the numbers you see are to be treated as text, but they are in a cell that is formatted as a “number.”  You can verify this by right-clicking on the cell, selecting “Format cells” and then clicking on the “Numbers” tab, as illustrated in the screenshots below:

apostrophe problem - 03

apostrophe problem - 04

So, the spreadsheet program is treating a series of number as text in a cell formatted as numbers.  Talk about aggravating.

The Solution:

There is a solution for this.  It involves a “find and replace” function.  From the “Edit” menu, choose “Find & Replace.”  In the “Find” box, type “^.” (i.e., carat + period).  In the “Replace” box, type “&” (without the quotes, of course).  But you have to do one more thing for this to work.  At the bottom of the “Find & Replace” window you’ll see a button that says “More Options.”   Click on it, then select the box next to “Regular Expressions.”  You should now have a “Find & Replace” box that looks like this:

apostrophe problem - 05

Now, hit “Replace All” and all of your apostrophes will disappear.  Your series of numbers will now be seen as numbers by your spreadsheet program and will be in cells formatted as number cells.  Problem solved.

Notes:

I actually don’t know how/why this works, but it does.  I found the solution here.

Comments (96)

  1. carl said on 14-11-2012

    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 said on 28-11-2012

    @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. Anonymous said on 02-12-2012

    Thanks Danke thanks thanks a lot!!!!

  4. Mike said on 13-12-2012

    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?

  5. FLOG51 said on 16-01-2013

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

  6. BigBob said on 18-01-2013

    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!

  7. cmbmcn said on 30-01-2013

    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).

  8. PB said on 21-02-2013

    @cmbmcn
    Text to Columns solved it for me ! You have no idea how annoying this issue has been (maybe you have). Thanks!

  9. NAumond said on 25-02-2013

    Thank you so much!

  10. CB said on 30-03-2013

    Thank you!

  11. Caminosurf said on 05-04-2013

    Vielen Dank – many thanks. Just the hint i needed!
    The work of all out there doing things like this is very much appreciated!

  12. Upen said on 18-04-2013

    Many Thanks Dear… My Problem gets solved using your solution. Once again thanks

  13. Jake Brumby said on 23-05-2013

    That worked a treat. I’ve been putting up with this issue for years. Now I know! Thanks a lot.

  14. AT said on 07-06-2013

    Thanks dear. This is exactly what i was looking for.
    Thanks a lot!

  15. jj said on 09-06-2013

    THANK YOU!

  16. Deb said on 18-06-2013

    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..)!

  17. Natalie Bjorklund said on 20-06-2013

    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!

  18. Joseph said on 25-06-2013

    Thanks for sharing!

  19. Tmpe said on 04-07-2013

    still a great tip, saved me a lot of manual entries. thanks!

  20. Victoria Whitehead said on 05-07-2013

    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.

  21. eviriyanti said on 26-07-2013

    Thanks a lot, its really helpfull :)

  22. albert said on 21-08-2013

    Great time saver. Thanks for posting this info.

  23. Andy said on 04-09-2013

    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! :)

  24. jhunt29 said on 14-10-2013

    Just an explanation of why the find and replace with “^.” and “&” works. A list of wildcards used in regular expressions in open office can be found at https://help.libreoffice.org/Common/List_of_Regular_Expressions. Essentially, you are searching for the first character in every cell, and altering it if it is a format mark

  25. Gregg said on 18-10-2013

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

  26. Chris Fotiou said on 22-10-2013

    Awesome! Works well! Thank you for the solution.

  27. Mr Plumber said on 27-10-2013

    Another Happy customer

  28. Ani Papyan said on 01-11-2013

    It really helps! Thanks a lot!

  29. Tosh said on 14-11-2013

    Thank you for taking to the time to post a fix for this issue. Worked perfectly!

  30. Jelle said on 15-11-2013

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

  31. Andy said on 20-11-2013

    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.

  32. Tim said on 29-11-2013

    THANK YOU!!!!!!!! This is so dumb and your solution worked perfectly!!

  33. Andrew said on 10-12-2013

    Cool!
    Still need in Libre Office!

  34. Gokul ME said on 26-12-2013

    Awesome..Thanks a lot

  35. Jake said on 29-12-2013

    Thanks a ton. This fix just saved me a ton of time!

  36. si said on 19-01-2014

    This worked for me while Find & Replace did not. Thank you so much.

  37. Lisa said on 01-02-2014

    THANK. YOU. SO. MUCH.

  38. adi pharadisze said on 03-02-2014

    thank you. it works

  39. Duryodhan said on 13-02-2014

    Beutiful Time saver formula. Thanks a lot

  40. Jimbo said on 15-02-2014

    Thanks! Your solution helped me too!

  41. Sindy said on 23-02-2014

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

  42. Jans said on 27-02-2014

    Aaah, finally a page that’s helpful! Thamx!

  43. Judijo said on 05-03-2014

    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

  44. Bho said on 27-03-2014

    add another grateful thanks. you save me a LOT of time!

Leave a Reply

Your email address will not be published.