remove apostrophes before numbers in OpenOffice Calc

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 treats a series of numbers 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.

Loading


Posted

in

by

Tags:

Comments

167 responses to “remove apostrophes before numbers in OpenOffice Calc”

  1. jzacsh Avatar
    jzacsh

    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?

  2. ryan Avatar

    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.

    1. jzacsh Avatar
      jzacsh

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

  3. Tony Avatar
    Tony

    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 😛

  4. Mike Avatar
    Mike

    Hey, this worked great for us. Thanks

  5. Piotr Avatar
    Piotr

    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 🙂

    1. meehow Avatar
      meehow

      Awesome. Thank you. that worked!

    2. SJ Ikle-Khalsa Avatar
      SJ Ikle-Khalsa

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

  6. Pier Avatar
    Pier

    WOW AWESOME!!! It works!!!!

  7. cirovladimir Avatar

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

  8. Josefin Avatar
    Josefin

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

  9. Jeff Walters Avatar

    Great solution, thanks!

  10. Kevin Avatar
    Kevin

    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.

    1. ryan Avatar

      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

  11. ryan Avatar

    @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

  12. Laura Avatar
    Laura

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

  13. Joseph Avatar

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

  14. Sage Avatar
    Sage

    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

  15. Zdenek Avatar
    Zdenek

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

    Thank you.

  16. Rob Avatar
    Rob

    You are awesome. Saved me forever.

  17. Diane Avatar
    Diane

    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.

  18. Scott Avatar
    Scott

    Thank you so much! This was driving me crazy!

  19. Waldir Avatar
    Waldir

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

  20. Michael S> Avatar
    Michael S>

    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”

  21. james breiner Avatar

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

  22. Thomas B. Higgins Avatar
    Thomas B. Higgins

    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.

  23. nambot Avatar
    nambot

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

  24. NJBILLT Avatar
    NJBILLT

    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.

  25. Joe P. Avatar
    Joe P.

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

  26. Luke Avatar
    Luke

    been looking for this for an eternity. thanks

  27. JR Avatar

    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!

  28. Ethan Avatar
    Ethan

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

  29. Aubrey Avatar
    Aubrey

    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

  30. Zachary Avatar

    Just what I needed. Thanks a bunch! 🙂

  31. Zoltan Avatar
    Zoltan

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

  32. Steve Avatar
    Steve

    You are a god!

  33. Jon Avatar

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

  34. Dan Avatar
    Dan

    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?

  35. ryan Avatar

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

  36. Vin Avatar
    Vin

    Thanks a lot.It worked for me

  37. Christie Avatar
    Christie

    Thanks! That was driving me crazy!

  38. moqauvin Avatar
    moqauvin

    @NJBILLT thank you thank you!

  39. kaimana Avatar
    kaimana

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

  40. John Poole Avatar

    Worked like a charm. THANKS! 😉

  41. Scooter Avatar
    Scooter

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

  42. John314 Avatar
    John314

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

  43. SkEyesOGrey Avatar
    SkEyesOGrey

    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?

  44. ryan Avatar

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

  45. A. Peon Avatar
    A. Peon

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

  46. Bill Avatar
    Bill

    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?

  47. carl Avatar
    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!

  48. teum Avatar
    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.

  49.  Avatar
    Anonymous

    Thanks Danke thanks thanks a lot!!!!

  50. Mike Avatar
    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?

  51. FLOG51 Avatar
    FLOG51

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

  52. BigBob Avatar
    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!

  53. cmbmcn Avatar
    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).

  54. PB Avatar
    PB

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

  55. NAumond Avatar
    NAumond

    Thank you so much!

  56. CB Avatar
    CB

    Thank you!

  57. Caminosurf Avatar

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

  58. Upen Avatar
    Upen

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

  59. Jake Brumby Avatar
    Jake Brumby

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

  60. AT Avatar
    AT

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

  61. jj Avatar
    jj

    THANK YOU!

  62. Deb Avatar
    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..)!

  63. Natalie Bjorklund Avatar
    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!

  64. Joseph Avatar
    Joseph

    Thanks for sharing!

  65. Tmpe Avatar
    Tmpe

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

  66. Victoria Whitehead Avatar

    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.

  67. eviriyanti Avatar
    eviriyanti

    Thanks a lot, its really helpfull 🙂

  68. albert Avatar
    albert

    Great time saver. Thanks for posting this info.

  69. Andy Avatar

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

  70. jhunt29 Avatar
    jhunt29

    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

  71. Gregg Avatar
    Gregg

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

  72. Chris Fotiou Avatar

    Awesome! Works well! Thank you for the solution.

  73. Mr Plumber Avatar
    Mr Plumber

    Another Happy customer

  74. Ani Papyan Avatar
    Ani Papyan

    It really helps! Thanks a lot!

  75. Tosh Avatar
    Tosh

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

  76. Jelle Avatar
    Jelle

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

  77. Andy Avatar

    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.

  78. Tim Avatar
    Tim

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

  79. Andrew Avatar
    Andrew

    Cool!
    Still need in Libre Office!

  80. Gokul ME Avatar
    Gokul ME

    Awesome..Thanks a lot

  81. Jake Avatar
    Jake

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

  82. si Avatar
    si

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

  83. Lisa Avatar
    Lisa

    THANK. YOU. SO. MUCH.

  84. adi pharadisze Avatar
    adi pharadisze

    thank you. it works

  85. Duryodhan Avatar
    Duryodhan

    Beutiful Time saver formula. Thanks a lot

  86. Jimbo Avatar
    Jimbo

    Thanks! Your solution helped me too!

  87. Sindy Avatar
    Sindy

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

  88. Jans Avatar
    Jans

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

  89. Judijo Avatar
    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

  90. Bho Avatar
    Bho

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

  91. Ajay Avatar
    Ajay

    It’s Works Thanks

  92. Magdalaena Avatar
    Magdalaena

    Thank You!

  93. dave Avatar
    dave

    wow…it works

  94. Nishikant Prabhu Avatar
    Nishikant Prabhu

    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,

  95.  Avatar
    Anonymous

    Thanks Very Much Solved my problem immediately

    Chetan Mahajan

  96. Daniele Avatar
    Daniele

    Thaaaaank you! Great!

  97. gurcanyavuz Avatar
    gurcanyavuz

    thank you. it save my work

  98. Nutang Avatar
    Nutang

    Thank a lot Kra.

  99. ChaCha Avatar
    ChaCha

    Extremely useful.
    Great
    Thank you

  100. aap Avatar
    aap

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

  101. Dottore Avatar
    Dottore

    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.

    1. Luca S Avatar
      Luca S

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

  102. Tracey Hand Avatar

    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

  103. Antonio Avatar
    Antonio

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

  104. Plannenmaker Avatar
    Plannenmaker

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

    1. Plannenmaker Avatar
      Plannenmaker

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

    2. Rahul Khurana Avatar
      Rahul Khurana

      great thanks a loooooooooooooot

  105. Gergely Tomanovics Avatar
    Gergely Tomanovics

    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)

  106. Phil.I.P. Avatar
    Phil.I.P.

    Thank you so much!

    This issue was getting me seriously annoyed.

  107. Heitor Avatar
    Heitor

    THANK
    GOD

  108. d Avatar
    d

    thank you !!!

  109. litterbug Avatar
    litterbug

    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.

    1. litterbug Avatar
      litterbug

      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.

      1.  Avatar
        Anonymous

        Thank you. This worked for me

  110. NanoSteve Avatar
    NanoSteve

    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-

  111. B Avatar
    B

    Brilliant.
    Many thanks….

  112. Lanny93 Avatar
    Lanny93

    Selecting “Detect Special Characters” worked for me

    1. Lanny93 Avatar
      Lanny93

      *I meant “Detect special numbers”

  113. Nagesh Avatar
    Nagesh

    Thanks ! it worked

  114. Marcus Avatar
    Marcus

    Thanks, very useful.

  115. Dan Miller Avatar
    Dan Miller

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

  116. Andreas Avatar
    Andreas

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

  117. Marvin Wedel Avatar
    Marvin Wedel

    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

  118. EBM Avatar
    EBM

    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.

  119. Gary Goetz Avatar
    Gary Goetz

    Awesome thanks!

  120. Jason Avatar
    Jason

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

  121. Maanonmars Avatar
    Maanonmars

    Wow great find. Kudos.

  122. Cristina Figueiredo Valente Avatar
    Cristina Figueiredo Valente

    Thankyouu!!

  123.  Avatar
    Anonymous

    You’re a life saver! Many thanks.

  124. Basheer Noorgat Avatar
    Basheer Noorgat

    Thanks a lot. Saved me time!

  125. Noname Avatar
    Noname

    Thanks a lot! It works well.

  126. Gilmour Avatar
    Gilmour

    Magic Right in front of my eyes

    Thanks

  127. Piyush Avatar
    Piyush

    great thanks

  128.  Avatar
    Anonymous

    thanks it’s working.

  129. thanksalot Avatar
    thanksalot

    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…

  130. Kent Avatar

    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 🙂

  131. Justin Avatar
    Justin

    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.

  132. Claire Avatar
    Claire

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

  133. BenJamminEsq Avatar
    BenJamminEsq

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

    1. ryan Avatar

      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?

  134. John Avatar
    John

    Thanks! You saved a life today!

  135. Sonja Avatar
    Sonja

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

  136. Carol Avatar
    Carol

    Thank you sooooo much!

  137. KARA Avatar
    KARA

    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!

  138. Alex Avatar

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

  139. Steve Avatar
    Steve

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

  140. Raul Avatar
    Raul

    Muchas Gracias!

  141. Julia James Avatar
    Julia James

    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.

  142. Dee Avatar
    Dee

    Brilliant! Thank you!

  143. Jakub Muszyński Avatar
    Jakub Muszyński

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

  144. Sanjeet Mann Avatar
    Sanjeet Mann

    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!

  145. Mike Avatar
    Mike

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

  146. Paul Avatar
    Paul

    Thank you
    Paul

  147. Margo Avatar
    Margo

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

  148. Dmytro Avatar
    Dmytro

    Thank you, it works!

  149. DaveC426913 Avatar
    DaveC426913

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

  150. David Avatar
    David

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

  151. Prem Kumar Avatar
    Prem Kumar

    You are a STAR!!!

  152. Tristan Avatar
    Tristan

    Thanks so much for posting this. I’ve spent the last hour trying to solve this and your solution worked a treat for me! Ta!

  153. Alan Avatar
    Alan

    Great post, 15 years later it is still helping people!
    I had a clumsy workaround but it wasn’t nearly as quick as your and some posters suggestions.

    Alan,
    Seattle

  154. Russell Avatar

    Amazing Help! Thank you!! I had so much trouble with this last year & had to look it up again. I found this. The “Text to columns” trick works so fast & easy.

  155. Julius Rosen Avatar
    Julius Rosen

    omg – perfect – everything else was SO complicated …. I am trying to just calc retail prices and this kept happening! 12 years old and still great advice … THANK YOU

Leave a Reply to Carol Cancel reply

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