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.
Thaaaaank you! Great!
thank you. it save my work
Thank a lot Kra.
Extremely useful.
Great
Thank you
That’s exactly what I’ve been searching for. Huge thanks!
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.
Thank you for the insight! I couldn’t figure out the meaning of the ampersand
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
Just awesome. Works exactly as said. Thank you very much, it saved me! ¡Muchas gracias!
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!!!
Update: works in Kingsoft, but not in Planmaker….sigh….
great thanks a loooooooooooooot
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)
Thank you so much!
This issue was getting me seriously annoyed.
THANK
GOD
thank you !!!
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.
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.
Thank you. This worked for me
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-
Brilliant.
Many thanks….
Selecting “Detect Special Characters” worked for me
*I meant “Detect special numbers”
Thanks ! it worked
Thanks, very useful.
That was *amazingly* obtuse (the problem, not your solution).
Thank you, I was about to delete OpenOffice over this.
Awesome…… have been surging for hours to get a trick for that problem………….. Thank You Mister…….. Great Job!!!
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
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.
Awesome thanks!
I know this is an ancient post by internet standards but this solution still works! Thanks for saving me a big headache!
Wow great find. Kudos.
Thankyouu!!
You’re a life saver! Many thanks.
Thanks a lot. Saved me time!
Thanks a lot! It works well.
Magic Right in front of my eyes
Thanks
great thanks
thanks it’s working.
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…
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 :-)
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.
Thanks so much saved heeeps of work. We have told the bank help line who couldn’t help
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”.
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?
Thanks! You saved a life today!
Thank you so much, this was exactly what I needed!
Thank you sooooo much!
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!
thank you SOOOOOOO MUCHHH !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thank you very much! It saved me a lot of time.
Muchas Gracias!
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.
Brilliant! Thank you!