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:
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:
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:
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:
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.
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?
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.
Thanks! I started a post on oOo forums and got help. This got me on the write track though, thanks!
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 :P
Hey, this worked great for us. Thanks
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 :)
WOW AWESOME!!! It works!!!!
@Piotr
Thank you very much. I’m wondering what’s the purpose of that option. Nevertheless it worked like a charm.
I love you for this!!!! Thanks! :D
Great solution, thanks!
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.
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
@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
Oh THANK YOU! This has been driving me completely bats! Worked like a charm :)
Bravo! This was wasting me an hour every week untill you solotuion worked :) Now I can love Open Office again
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
Hey! This helped me a lot!
My document has 850.000 lines with these invisible apostrophs.
Thank you.
You are awesome. Saved me forever.
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.
Thank you so much! This was driving me crazy!
You’re the man, Ryan! Saved me quite a few hours of monkey work. Thanks!
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”
You people are awesome. Thank you. This saved me a ton of work.
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.
OMG. I love you. You just saved me hours of time. Thanks!
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.