Skip to content
Menu
Ryan and Debi & Toren
  • highpoints
  • Privacy Policy
  • R
  • tech
  • Where I’ve Been
Ryan and Debi & Toren

remove apostrophes before numbers in OpenOffice Calc

Posted on November 29, 2009

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.

 49,705 total views,  21 views today

168 thoughts on “remove apostrophes before numbers in OpenOffice Calc”

  1. jzacsh says:
    December 4, 2009 at 2:50 pm

    this doesn’t work for me, the search feature doesn’t find anything. I thought these were “invisible apostrophes”? Doesn’t that mean that they’re not search-able, as they’re not normal?

    Reply
  2. ryan says:
    December 4, 2009 at 11:08 pm

    Did you choose “regular expressions” at the bottom of the find/replace box? If not, it won’t find them.

    Also, from what I understand from the site where I got this, this doesn’t actually find and replace the apostrophes. What it actually does is recalculate all of the cells with numbers in them, turning them into numbers.

    If it’s still not working, not sure what to tell you.

    Reply
    1. jzacsh says:
      December 4, 2009 at 11:21 pm

      Thanks! I started a post on oOo forums and got help. This got me on the write track though, thanks!

      Reply
  3. Tony says:
    December 8, 2009 at 7:05 am

    Thanks a ton! I’ve been searching for 2 hours on this (not the apostrophe) and couldn’t find any help. Finally I noticed the ‘ in front of the numbers and started in with the searches. Found your first in google and can now stop pulling my hair out 😛

    Reply
  4. Mike says:
    February 10, 2010 at 4:55 pm

    Hey, this worked great for us. Thanks

    Reply
  5. Piotr says:
    April 2, 2010 at 1:05 pm

    Hey, there is simpler solution.
    Just select column with these numbers treated as text, choose Data -> Text to Columns.
    Automagically, it converts them all to numbers.

    But I didn’t find it myself, my wife did 🙂

    Reply
    1. meehow says:
      July 11, 2022 at 5:40 pm

      Awesome. Thank you. that worked!

      Reply
    2. SJ Ikle-Khalsa says:
      December 1, 2022 at 2:51 am

      The original solution worked for me. but thanks for also sharing a slightly easier one (which also worked). but i did have to also change the “text delimiter” from a quotation mark (“) to an apostrophe (‘).

      Reply
  6. Pier says:
    May 21, 2010 at 12:35 pm

    WOW AWESOME!!! It works!!!!

    Reply
  7. cirovladimir says:
    May 26, 2010 at 10:32 am

    @Piotr
    Thank you very much. I’m wondering what’s the purpose of that option. Nevertheless it worked like a charm.

    Reply
  8. Pingback: OpenOffice – Remover el Apostrofe « Blogging googling
  9. Pingback: Ryananddebi
  10. Josefin says:
    March 16, 2011 at 10:08 am

    I love you for this!!!! Thanks! 😀

    Reply
  11. Pingback: Ryan, Debi, and Toren Cragun’s Blog » remove apostrophes before numbers in OpenOffice Calc | Rough Drafts of a Koanic Soul
  12. Jeff Walters says:
    April 13, 2011 at 12:58 pm

    Great solution, thanks!

    Reply
  13. Kevin says:
    May 14, 2011 at 9:35 am

    Say, has anyone had trouble with apostrophes appearing not in front of but behind numbers? Again, the column says it is a number; however, no matter what I do, including the solutions above, the darn apostrophe keeps appearing when I enter a number in a blank cell. I can manually delete it, but what a pain.

    Reply
    1. ryan says:
      May 16, 2011 at 8:24 am

      Hi Kevin,

      I’ve never run into this problem. Can you describe what you are doing to make it occur in greater detail?

      Best,

      Ryan

      Reply
  14. ryan says:
    May 16, 2011 at 8:26 am

    @Kevin
    Hi Kevin,

    I’ve never run into this problem. Can you describe what you are doing to make it occur in greater detail?

    Best,

    Ryan

    Reply
  15. Laura says:
    July 21, 2011 at 8:38 am

    Oh THANK YOU! This has been driving me completely bats! Worked like a charm 🙂

    Reply
  16. Joseph says:
    August 3, 2011 at 8:20 am

    Bravo! This was wasting me an hour every week untill you solotuion worked 🙂 Now I can love Open Office again

    Reply
  17. Sage says:
    October 18, 2011 at 12:55 pm

    Oh my Gosh. I’ve been trying to isolate the apostrophe itself. I’ve spent ~five hours on this, trying to learn regular expressions. This was all it took?! Thankyouthankyouthankyouthankyouthankyou

    Reply
  18. Zdenek says:
    October 21, 2011 at 12:13 pm

    Hey! This helped me a lot!
    My document has 850.000 lines with these invisible apostrophs.

    Thank you.

    Reply
  19. Rob says:
    October 23, 2011 at 4:51 am

    You are awesome. Saved me forever.

    Reply
  20. Diane says:
    October 28, 2011 at 10:10 am

    This should be part of the FAQs on OpenOffice’s website.
    Amazing. I consider myself a pretty good Googler / help file searcher. Just lucky my Google Fu turned up your site as the OpenOffice site and help files were no help.

    Thank you so much for your very clear instructions! Two year old post and it still applies.

    Reply
  21. Scott says:
    November 2, 2011 at 2:33 pm

    Thank you so much! This was driving me crazy!

    Reply
  22. Waldir says:
    November 2, 2011 at 4:37 pm

    You’re the man, Ryan! Saved me quite a few hours of monkey work. Thanks!

    Reply
  23. Michael S> says:
    December 2, 2011 at 8:04 pm

    It is really infuriating how Calc simply ignores the column or cell properties (me forcing it to be ciphers or valuta) and keeps quoting certain numbers like 3.59
    –> ‘3.59 but (most) others in the same column as ciphers the way I wanted them. Manually removing that heinous ‘ will turn 3.59 into 21610,00!!!! Even when carefully removing all digits, pasting them in notepad and pasting etc. won’t get rid of the %##%@! quotes so my calculations simply return 0. It really defies the whole purpose of Open Office if it is so fundamentally messing up in one of the bare essentials of a spreadsheet program. Why this ‘3.59 and ‘10.00 became to be text in stead of ciphers I’ll never know because the original CSV that was imported did not contain any aberrations in that column. Obviously it is an error in the import logarithms used and probably would qualify as ‘bugz’. Anyway, thanks for the tips that I have found here, it has indeed cleared those ridiculous (‘random’) quotes!!! I am glad that I did not have to waste hours on finding a how to. For your information my search engine query was: “open office how to delete quoted values”

    Reply
  24. james breiner says:
    December 29, 2011 at 4:25 pm

    You people are awesome. Thank you. This saved me a ton of work.

    Reply
  25. Thomas B. Higgins says:
    January 7, 2012 at 2:21 pm

    Ryan, Thank you very much for this. The last time I had this problem and searched for a solution I found nothing. Your clear, well illustrated advice makes the world a better place.

    Reply
  26. nambot says:
    January 17, 2012 at 6:40 pm

    OMG. I love you. You just saved me hours of time. Thanks!

    Reply
  27. NJBILLT says:
    January 18, 2012 at 10:06 am

    This might help someone too. I saw someone asking about if it’s AFTER the text. Well, I copied my credit card into calc and one column wouldn’t add. So I figured out that it’s text too just like the above problem, but in my case, no apostrophe. It’s a SPACE, and it’s AFTER the numbers, not before, so it’s not so easy to find. Solution: use find and replace. COPY the space from calc, and replace it with a 0 (zero) Now it will format like numbers. If you’re formatted for 2 decimal points, you won’t even see the extra 0 you added.

    Reply
  28. Joe P. says:
    February 22, 2012 at 9:37 pm

    Great Solution! Thanks for saving me from a definite headache.

    Reply
  29. Luke says:
    March 11, 2012 at 3:27 am

    been looking for this for an eternity. thanks

    Reply
  30. JR says:
    March 16, 2012 at 12:43 am

    THANK YOU 1000 times! You really saved me, I was doing ti manually and it was driving me crazy, thank you, thank you, thank you!

    Reply
  31. Ethan says:
    March 30, 2012 at 4:24 pm

    Thank you kind denizens of the web. This solved my ever so annoying ‘apostrophe’ problem just in time for tax season.

    Reply
  32. Aubrey says:
    April 4, 2012 at 2:17 pm

    Thank you!! This will save me hours on my taxes. I’ve just spent 1/2 hour trying to solve this on my own. :p

    Reply
  33. Zachary says:
    April 11, 2012 at 11:13 pm

    Just what I needed. Thanks a bunch! 🙂

    Reply
  34. Zoltan says:
    May 1, 2012 at 1:47 pm

    This was driving me nuts until I found this. Thanks for the headache relief!

    Reply
  35. Steve says:
    June 13, 2012 at 12:41 am

    You are a god!

    Reply
  36. Jon says:
    July 13, 2012 at 6:03 am

    You my friend, are a saint. Thank you so much for taking the time to share this and help others..gives me hope (and a working bloody spreadsheet…).

    Reply
  37. Dan says:
    August 11, 2012 at 5:17 am

    Arrrrgh! None of this is working here. When I remove apostrophes by any method, or use data>text to columns, all my dates turn into “###”
    Any ideas?

    Reply
  38. ryan says:
    August 11, 2012 at 3:09 pm

    @Dan
    Are you having the problem outlined in this post? Or is your problem different? Do you want to send your spreadsheet to me?

    Reply
  39. Vin says:
    August 22, 2012 at 5:12 am

    Thanks a lot.It worked for me

    Reply
  40. Christie says:
    August 25, 2012 at 11:31 am

    Thanks! That was driving me crazy!

    Reply
  41. moqauvin says:
    August 25, 2012 at 6:03 pm

    @NJBILLT thank you thank you!

    Reply
  42. kaimana says:
    August 31, 2012 at 3:34 pm

    You have officially saved my life!!! Thank you!!!!!

    Reply
  43. John Poole says:
    September 12, 2012 at 4:07 pm

    Worked like a charm. THANKS! 😉

    Reply
  44. ryan says:
    October 8, 2012 at 12:33 pm

    @Scooter
    Happy to help.

    Reply
  45. Scooter says:
    October 8, 2012 at 11:52 am

    This is the most clear and concise instructional post I have ever read, and solved my aggravating problem in 1-minute. Thanks author!

    Reply
  46. John314 says:
    October 29, 2012 at 11:43 am

    Thank you! I thought my formula was messed up but it was really the hidden apostrophe. I appreciate it greatly.

    Reply
  47. SkEyesOGrey says:
    October 31, 2012 at 12:23 am

    Forgive me for looking a gift horse in the mouth, this saved me pulling out all my hair. With mine, it adds the apostrophe each time a check box is clicked. Best I can tell I have to do the find and replace each time the boxes are clicked. Has anyone found a more permanent solution?

    Reply
  48. ryan says:
    October 31, 2012 at 9:01 am

    @SkEyesOGrey
    Hmm… Never seen the situation you’re describing. I wonder if it has to do with the programming behind the check box. If so, not sure how to fix that.

    Reply
  49. A. Peon says:
    November 5, 2012 at 12:30 pm

    @Dan

    Drag the column wider.

    If you see crazy numbers instead of dates, right click on the column and change the number format to ‘date’ – it uses a wacky UNIX epoch type system for them if you view them as integers/floating point.

    Reply
  50. Bill says:
    November 6, 2012 at 9:27 pm

    Thank you!

    To SkEyesOGre (who said that an apostrophe gets added), have you first formatted the column to make sure it’s in the “Number” format?

    Reply
  51. carl says:
    November 14, 2012 at 9:47 am

    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!

    Reply
  52. teum says:
    November 28, 2012 at 7:05 am

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

    Reply
  53. Anonymous says:
    December 2, 2012 at 9:36 pm

    Thanks Danke thanks thanks a lot!!!!

    Reply
  54. Mike says:
    December 13, 2012 at 3:11 pm

    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?

    Reply
  55. FLOG51 says:
    January 16, 2013 at 10:39 pm

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

    Reply
  56. BigBob says:
    January 18, 2013 at 12:07 pm

    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!

    Reply
  57. cmbmcn says:
    January 30, 2013 at 8:34 pm

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

    Reply
  58. PB says:
    February 21, 2013 at 9:04 am

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

    Reply
  59. NAumond says:
    February 25, 2013 at 7:35 pm

    Thank you so much!

    Reply
  60. CB says:
    March 30, 2013 at 6:03 pm

    Thank you!

    Reply
  61. Caminosurf says:
    April 5, 2013 at 5:14 pm

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

    Reply
  62. Upen says:
    April 18, 2013 at 12:59 am

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

    Reply
  63. Jake Brumby says:
    May 23, 2013 at 8:59 am

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

    Reply
  64. AT says:
    June 7, 2013 at 7:33 am

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

    Reply
  65. jj says:
    June 9, 2013 at 9:06 am

    THANK YOU!

    Reply
  66. Deb says:
    June 18, 2013 at 11:03 am

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

    Reply
  67. Natalie Bjorklund says:
    June 20, 2013 at 10:05 am

    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!

    Reply
  68. Joseph says:
    June 25, 2013 at 11:29 am

    Thanks for sharing!

    Reply
  69. Tmpe says:
    July 4, 2013 at 8:58 am

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

    Reply
  70. Victoria Whitehead says:
    July 5, 2013 at 10:19 am

    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.

    Reply
  71. eviriyanti says:
    July 26, 2013 at 4:14 am

    Thanks a lot, its really helpfull 🙂

    Reply
  72. albert says:
    August 21, 2013 at 2:29 pm

    Great time saver. Thanks for posting this info.

    Reply
  73. Andy says:
    September 4, 2013 at 3:33 pm

    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! 🙂

    Reply
  74. jhunt29 says:
    October 14, 2013 at 5:39 pm

    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 . Essentially, you are searching for the first character in every cell, and altering it if it is a format mark

    Reply
  75. Gregg says:
    October 18, 2013 at 5:59 pm

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

    Reply
  76. Chris Fotiou says:
    October 22, 2013 at 1:00 am

    Awesome! Works well! Thank you for the solution.

    Reply
  77. Mr Plumber says:
    October 27, 2013 at 7:04 pm

    Another Happy customer

    Reply
  78. Ani Papyan says:
    November 1, 2013 at 2:21 am

    It really helps! Thanks a lot!

    Reply
  79. Tosh says:
    November 14, 2013 at 12:45 pm

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

    Reply
  80. Jelle says:
    November 15, 2013 at 4:50 am

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

    Reply
  81. Andy says:
    November 20, 2013 at 7:19 am

    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.

    Reply
  82. Tim says:
    November 29, 2013 at 1:18 pm

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

    Reply
  83. Andrew says:
    December 10, 2013 at 6:26 pm

    Cool!
    Still need in Libre Office!

    Reply
  84. Gokul ME says:
    December 26, 2013 at 9:43 am

    Awesome..Thanks a lot

    Reply
  85. Jake says:
    December 29, 2013 at 5:27 pm

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

    Reply
  86. si says:
    January 19, 2014 at 8:31 am

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

    Reply
  87. Lisa says:
    February 1, 2014 at 7:02 pm

    THANK. YOU. SO. MUCH.

    Reply
  88. adi pharadisze says:
    February 3, 2014 at 8:48 pm

    thank you. it works

    Reply
  89. Duryodhan says:
    February 13, 2014 at 11:00 pm

    Beutiful Time saver formula. Thanks a lot

    Reply
  90. Jimbo says:
    February 15, 2014 at 1:50 pm

    Thanks! Your solution helped me too!

    Reply
  91. Sindy says:
    February 23, 2014 at 7:50 pm

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

    Reply
  92. Jans says:
    February 27, 2014 at 6:34 am

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

    Reply
  93. Judijo says:
    March 5, 2014 at 9:57 am

    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

    Reply
  94. Bho says:
    March 27, 2014 at 8:08 pm

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

    Reply
  95. Ajay says:
    May 14, 2014 at 9:03 am

    It’s Works Thanks

    Reply
  96. Magdalaena says:
    May 27, 2014 at 4:38 pm

    Thank You!

    Reply
  97. dave says:
    July 3, 2014 at 4:39 am

    wow…it works

    Reply
  98. Nishikant Prabhu says:
    July 18, 2014 at 8:42 am

    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,

    Reply
  99. Anonymous says:
    August 1, 2014 at 5:41 am

    Thanks Very Much Solved my problem immediately

    Chetan Mahajan

    Reply
  100. Pingback: Replacing apostrophe in OpenOffice calc | mpituley
  101. Daniele says:
    August 28, 2014 at 5:05 am

    Thaaaaank you! Great!

    Reply
  102. gurcanyavuz says:
    September 9, 2014 at 3:12 pm

    thank you. it save my work

    Reply
  103. Pingback: open ofisteki say?lar?n önündeki kesme i?aretini silmek | gurcan yavuz
  104. Nutang says:
    September 29, 2014 at 10:26 pm

    Thank a lot Kra.

    Reply
  105. ChaCha says:
    October 15, 2014 at 3:55 pm

    Extremely useful.
    Great
    Thank you

    Reply
  106. aap says:
    October 24, 2014 at 7:11 am

    That’s exactly what I’ve been searching for. Huge thanks!

    Reply
  107. Dottore says:
    October 28, 2014 at 12:40 am

    It works because it’s a regular expression.
    ^ means “at the beginning of the line”
    . means any character.

    so the “find” means to find the first character at the beginning of the line.

    This is replaced with an ampersand, which is the hidden character signifying a number.

    An apostrophe before a number tells Open Office to treat the number as text.

    Reply
    1. Luca S says:
      January 9, 2015 at 3:19 am

      Thank you for the insight! I couldn’t figure out the meaning of the ampersand

      Reply
  108. Tracey Hand says:
    November 3, 2014 at 7:15 am

    Awesome!
    This have been driving me crazy since installing Open Office on my Mac!
    Now I don’t have to do either of my previous options which were a) edit them all out or b) not sort by date when merging spreadsheets!
    Again, awesome!
    Thanks

    Reply
  109. Antonio says:
    December 6, 2014 at 6:28 pm

    Just awesome. Works exactly as said. Thank you very much, it saved me! ¡Muchas gracias!

    Reply
  110. Plannenmaker says:
    December 11, 2014 at 4:31 am

    What worked for me: (I’m using Kingsoft Spreadsheets but I think this works universally though)

    1) Copy entire column from spreadsheet
    2) Open Notepad (NOT Wordpad)
    3) Word-wrap should be OFF
    4) Paste column into Notepad and save as damn_you_apostrophe.txt
    5) Close Notepad and reopen
    6) CTRL-A then CTRL-C to copy entire contents of Notepad
    7) Paste in empty unformatted column in your favourite spreadsheet app.
    8) Apostrophe’s GONE!!!

    This took me only two days to discover LOL!!!

    Reply
    1. Plannenmaker says:
      December 11, 2014 at 4:41 am

      Update: works in Kingsoft, but not in Planmaker….sigh….

      Reply
    2. Rahul Khurana says:
      October 13, 2018 at 10:10 am

      great thanks a loooooooooooooot

      Reply
  111. Gergely Tomanovics says:
    December 14, 2014 at 8:12 am

    Awesome, thank you. Caused endless frustration for me to discover, what the hell is wrong with my calculations (which didn’t gave any result because that f***ing pointless apostrophe)

    Reply
  112. Phil.I.P. says:
    January 24, 2015 at 12:19 pm

    Thank you so much!

    This issue was getting me seriously annoyed.

    Reply
  113. Heitor says:
    January 24, 2015 at 11:14 pm

    THANK
    GOD

    Reply
  114. d says:
    April 5, 2015 at 1:49 pm

    thank you !!!

    Reply
  115. litterbug says:
    April 27, 2015 at 7:04 pm

    Yee-ha! Thanks a bunch! This will save me from endlessly finding/replace-all to correct both dates and numbers (e.g. find/replace 04/ with 04/ in a date, etc.) to get rid of the bleeping apostrophe so I can sort and add columns.

    BTW, today I managed to open one of those annoying files without the apostrophes by clicking some combination of boxes on the Text Import box that pops up when opening an Excel file. Unfortunately I haven’t been able to replicate what I did, but if I do I’ll try to remember to come back here and post the solution.

    Reply
    1. litterbug says:
      April 27, 2015 at 7:20 pm

      Another thing–another search result told me that selecting a column, choosing Data-Text to Columns, and clicking OK removes the apostrophes in that column. This only works one column at a time, but might feel more simple if you’re only dealing with one or two columns.

      Reply
      1. Anonymous says:
        February 15, 2018 at 6:03 am

        Thank you. This worked for me

        Reply
  116. NanoSteve says:
    June 10, 2015 at 5:03 pm

    I have been doing lots of this over the past week. My bet is that lots of people waste lots of time doing stuff like this. Keeps us slaves busy and not thinking dangerous thoughts. 😉

    Hey, I discovered that you can forget all the paste specials and manual removal of the text tick by simply copying the column into Notebook and then back where it came from. Nice, yes? 🙂 Wish I’d thought of that sooner.
    -NanoSteve-

    Reply
  117. B says:
    July 25, 2015 at 4:59 am

    Brilliant.
    Many thanks….

    Reply
  118. Lanny93 says:
    August 12, 2015 at 3:38 pm

    Selecting “Detect Special Characters” worked for me

    Reply
    1. Lanny93 says:
      August 12, 2015 at 3:38 pm

      *I meant “Detect special numbers”

      Reply
  119. Nagesh says:
    August 15, 2015 at 5:14 am

    Thanks ! it worked

    Reply
  120. Marcus says:
    August 17, 2015 at 7:47 am

    Thanks, very useful.

    Reply
  121. Dan Miller says:
    September 22, 2015 at 6:34 am

    That was *amazingly* obtuse (the problem, not your solution).
    Thank you, I was about to delete OpenOffice over this.

    Reply
  122. Andreas says:
    November 3, 2015 at 8:08 am

    Awesome…… have been surging for hours to get a trick for that problem………….. Thank You Mister…….. Great Job!!!

    Reply
  123. Marvin Wedel says:
    November 25, 2015 at 11:15 am

    I had the opposite problem. Lotus 123, save to xls, import to calc, dates are numbers but calc wants them as text (apostrophe) to use them in date calculations. Took me awhile to find that out, then how to add that apostrophe. Reverse above. find “^.” replace or replace all with “‘&” Options ‘Regular expressions’ ‘Selection only’ if needed.
    THANKS FOR THE HINT

    Reply
  124. EBM says:
    January 18, 2016 at 1:17 pm

    Another cheat I found. copy and paste numbers (text) into a document using Paste Special command and choose unformatted text. Then Copy and Paste back into your spreadsheet and there is a column of numbers to manipulate as normal.

    Reply
  125. Gary Goetz says:
    February 6, 2016 at 10:56 pm

    Awesome thanks!

    Reply
  126. Jason says:
    February 13, 2016 at 2:41 pm

    I know this is an ancient post by internet standards but this solution still works! Thanks for saving me a big headache!

    Reply
  127. Maanonmars says:
    February 27, 2016 at 4:39 pm

    Wow great find. Kudos.

    Reply
  128. Cristina Figueiredo Valente says:
    May 5, 2016 at 12:33 pm

    Thankyouu!!

    Reply
  129. Anonymous says:
    July 16, 2016 at 5:49 am

    You’re a life saver! Many thanks.

    Reply
  130. Basheer Noorgat says:
    August 23, 2016 at 7:27 am

    Thanks a lot. Saved me time!

    Reply
  131. Noname says:
    September 6, 2016 at 12:14 am

    Thanks a lot! It works well.

    Reply
  132. Gilmour says:
    September 13, 2016 at 1:49 pm

    Magic Right in front of my eyes

    Thanks

    Reply
  133. Piyush says:
    December 8, 2016 at 5:56 am

    great thanks

    Reply
  134. Anonymous says:
    January 11, 2017 at 7:37 am

    thanks it’s working.

    Reply
  135. thanksalot says:
    January 13, 2017 at 8:02 pm

    thanks that worked but I wanted to do it from a cell formula so what I did was if A1 had ’01/01/1990 for example, I typed in A2: =A1+0 and then used date as the format for that cell…

    Reply
  136. Kent says:
    January 21, 2017 at 1:32 pm

    Thanks so much for that and your down to earth explanation! It’s about time the programmers of this stuff stop trying to be so damn clever and make things practical and truly logical. My ‘ came in with a Paypal download in the date and time fields. Nearly went mad trying to sort my records in reverse date order. All okay now thanks to your knowledge thanks once again 🙂

    Reply
  137. Justin says:
    January 22, 2017 at 9:39 am

    Thank you so, so much. Every year, come this time (tax return / accounts hell) I get stuck with this issue, and I’ve always found myself having to re-type all dates in a second column as nothing would sort. So you a complete genius and I now have to print off your solution and file it in seventeen different places (and stick it on the wall and write it on my computer keyboard somewhere) or I’ll go mad trying to find it again next year…
    Massively grateful.

    Reply
  138. Claire says:
    April 6, 2017 at 10:20 pm

    Thanks so much saved heeeps of work. We have told the bank help line who couldn’t help

    Reply
  139. BenJamminEsq says:
    April 11, 2017 at 11:11 am

    So, did anyone here also get an error message first?

    After following the above advice the following error message flagged up “search key not found”.

    Reply
    1. ryan says:
      April 11, 2017 at 12:17 pm

      That error message would suggest that the software isn’t finding the hidden apostrophes. Are you sure you have numbers that are flagged as text with the hidden apostrophe?

      Reply
  140. John says:
    November 23, 2017 at 4:43 am

    Thanks! You saved a life today!

    Reply
  141. Sonja says:
    January 31, 2018 at 9:13 am

    Thank you so much, this was exactly what I needed!

    Reply
  142. Carol says:
    February 11, 2018 at 10:35 pm

    Thank you sooooo much!

    Reply
  143. KARA says:
    April 16, 2018 at 12:07 am

    Thank you! you are awesome. I was challenged and spent countless hours trying to change the asterick in front of my date column which had been saved as a text. I used this and it worked perfectly within seconds. TOTAL LIFESAVER!

    Reply
  144. Alex says:
    April 17, 2018 at 1:12 pm

    thank you SOOOOOOO MUCHHH !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Reply
  145. Steve says:
    May 6, 2018 at 12:43 pm

    Thank you very much! It saved me a lot of time.

    Reply
  146. Raul says:
    August 26, 2018 at 3:25 am

    Muchas Gracias!

    Reply
  147. Julia James says:
    August 26, 2018 at 8:30 am

    Thank you so much!!!! One question how did you figure this out? It makes no sense and is a combination I wouldn’t have come up with in a million years.

    Reply
  148. Dee says:
    February 22, 2019 at 1:00 pm

    Brilliant! Thank you!

    Reply
  149. Jakub Muszyński says:
    February 21, 2020 at 9:28 am

    Thank you for this!!! Works fine in open office too.

    Reply
  150. Sanjeet Mann says:
    February 22, 2020 at 12:51 am

    I used this tip to successfully convert text to date-formatted numbers in a 9,950 row spreadsheet, fixing a problem with date sorting in an ODB file based on the spreadsheet. Thank you very much for writing up the regex instructions!

    Reply
  151. Mike says:
    April 30, 2020 at 9:37 am

    Both the ‘find and replace’ and the ‘Data -> Text to Columns’ solutions work in LibreOffice too. Thank you!

    Reply
  152. Paul says:
    May 19, 2020 at 10:52 am

    Thank you
    Paul

    Reply
  153. Margo says:
    February 4, 2021 at 12:03 am

    THANK YOU SO MUCH!!! I WAS DOING IT MANUALLY AND IT WAS SO TEDIOUS!!

    Reply
  154. Dmytro says:
    July 22, 2021 at 2:10 pm

    Thank you, it works!

    Reply
  155. DaveC426913 says:
    August 12, 2021 at 6:09 pm

    You rock, Ryan! Saved my butt. Have 4000 records over 70 sheets, so have to have a bulk solution.
    (Man, this is the tip that keeps on giving, eh? 12 years and still going strong!)

    Reply
  156. David says:
    May 6, 2022 at 1:11 am

    It works! Your solution keeps on giving for many years. May you receive total consciousness upon your death.

    Reply
  157. Prem Kumar says:
    July 13, 2022 at 7:03 pm

    You are a STAR!!!

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • advice
  • country highpoints
  • funny stuff
  • general news
  • hiking
  • memories
  • movie reviews
  • opinions
  • other
  • politics
  • R
  • religion
  • sociology
  • state highpoints
  • stories
  • technology
  • Toren
  • travel
  • website feedback
©2023 Ryan and Debi & Toren | WordPress Theme by Superbthemes.com