when you import data from some external source and end up with numbers or dates in text format (withe the apostrophe as first character) I see no way to convert the cell contents to a different format except manually removing the '. sure there are some macros "out there" or you should have checked the data type during import. but sometimes you are in that situation and a nice way out would be nice.
There is an official extension that works: http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates *** This bug has been marked as a duplicate of bug 33962 ***
Well, since long time I see people, who are complaining Calc can not calc as expected. Discussing some complains I found this is because they can not realyze the availability of this apostrophe and/or can not understood why it is there (if they manage to found it). Also countless people every day are seaching the Internet for answers about this issue, and are creating scripts, trying many different things to solve this problem. If so it must be a importand problem and need to be addressed well if we would like to make LibreOffice better and nicier for the users. I belive the best way for solution will be: ------------------------------------------- 1. To change the Calc GUI form in Format → Cels → Numers and also change the current behaviour of it. It need to make possible to convert the cels data to numbers and to text seamlessly as for cels with '@' also for cells with apostrophe or else signs before the real data. (Now trying to change the formatting of such cells does nothing to change their formatting). I believe we should consider it as a bug. In future, clicking to change the formatting of cells must change their formatting as CT2N plugin is doing it, does not matter if they have '@', apostrophe or other unexpected sign in front of numbers. 2. If possible it will be great to reimplement in the LibreOffice GUI the functionality of the full functionality of CT2N (as in Version 1.4.0 of CT2N) instead to direct the people for searching the plugin in the Internet and put them to install it and make their life difficult this way. 3. Also it should be good to implement in Tools → Options → LibreOffice Calc → Formula under 'Detailed Calculation Settings' a functionality allowing the user to choose authomaticaly to solve the above issues with the cells consisting apostrophes etc. ... 4. Additionally some visual sygnalling need to be created to inform the users visually for such cells. This is needed because some users do not realyze the difference in such cells and even can not understood that calculations they see may be wrong, because of such apostrophes etc. ... As a paliative idea I suggest to make Calc to fill such cells with some short comments with warning about this apostrophe and idea what to do if they would change or remove it. But if such cell already have a comment it may be difficult. May be if such cells could be surrounded with moving dashed line (as with copy - paste operation) it may be helpfull too. Also such line may have different collor to differentiate from the cells in copy - paste operations? This is some thing that developers may decide how to do better. If above or similar steps could be done, then this pain for the regular users will be removed and the Calc will be more user friendly.
Please don't combine multiple requests into one - also for more information of how the project works (instead of saying "you need"): http://joelmadero.wordpress.com/2014/10/11/user-expectations-and-the-reality-of-our-community/