Bug 77317

Downstream bug report:
https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1305877

That's not a bug. Applying a cell format never changes the cell content. The ' apostrophe in the input line is prepended so that when editing the content it stays text. Removing the ' will accept the input as number then. You can bulk change multiple cells by selecting them, apply a number format and then invoke Find&Replace   Search: ^.   Replace: $0   and in options check regular expression and selection only. Or use the Convert-Text-To-Number extension from http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

For the record I'd like to note that this is a bizarre, unintuitive intended behaviour, and the fix you propose is equally bizarre and unintuitive. Having to install an extension seems way over the top for what should be a core functionality (I guess at some point someone was so desperate to do this that they resorted to coding the extension!).

I first attempted a find-replace for ' -> [empty], which didn't work. Your regex example will be totally opaque to nearly all users.

The answer to the pasting some data from a website problem is to Paste Special... get the delimiters right and make sure to check the box to detect special numbers (my data are here: http://physics.nist.gov/PhysRefData/XrayMassCoef/ComTab/pyrex.html ).

Hi Michael, even you don't like how it works, it is not a bug as Eike have explained, and he knows, as expert developer, about what he is talking.

Changing the cell format never change their content, unintuitive or not, this is a basic assumption in a spreadsheet, and I hope this will be always so.

If there are not formulas, you can copy the range and select the unformatted text option in the right arrow of the paste button. 
Or if it is only a column with data using Menu/Data/Text to columns.

If you are not agree, please open a new bug as a request for enhancement, detailing as much as possible what you want.

Next comment I attach a file sample how to extract the data from your web page, throuhg Menu/Insert/Link to external data.
You can see and edit the link in Menu/Edit/Links.

Created attachment 97240 [details]
Sample file linking data in a web page

Eike Rathke, thanks for taking a quick look at this.

Given when one comes from using Excel, where if one converted the Category from a Text to a Number, no apostrophe is appended, and how the LO code had designed an apostrophe in, this would be an "Excel functionality compatibility expectations" report. Hence, would the WORKAROUND you documented in https://bugs.freedesktop.org/show_bug.cgi?id=77317#c2 be documented on the LO wiki at http://help.libreoffice.org/Main_Page ?

m.a.riosv, suggesting the original reporter create now a third report (one downstream, this one, and your suggested third) because they didn't know it's an enhancement vs. a bug is a terrible habit of the OpenOffice.org bugzilla I've seen happen to others and personally experienced, and that we here at LibreOffice would not want to replicate.

Hence, I've gone ahead and marked this lowest enhancement given the advised WORKAROUNDs.

Thank you for your understanding.

Dear Eike, m.a.risov and Christopher,

Thanks for taking your time with this.

As a programmer concerned about user experience (which sometimes I get to practise, when not battling with spreadsheets), the userland behaviour is pretty weird. Why is there an apostrophe in the input line and not in the cell? That breaks all my assumptions about the relationship between the input line and the cell. For example, if I enter a ' into the input line, when a Text formatted cell is selected, I get a ' in the cell. Suddenly making a ' appear in the input line without it being in the cell is strange. Current behaviour seems like a cheap hack to get out of doing some actual conversion logic and user interaction. e.g.

1. Cell chars are all numerals, looks like an integer, no problem -> integer
2. Cell chars might look like a decimal number maybe with a , or a . or an E: ask the user what to do (locale-dependent sensible default required)
3. Function text is included (e.g. =, SUM(A1:A10), etc.) if it parses, -> no problem
4. Something else is there, random text, whatever. Then just apply number format to the cell and behave as though the user typed the same string of chars into the cell.

I hope that you can see that these solutions are, from a user perspective, somewhat more appealing than the current behaviour which is something like:

prepend a ', but only display it in the input line and not in the cell. Don't warn the user that this has happened because of an unwillingness to change the underlying datatype of the cell. Also don't tell them that they have to apply an obscure, terse regex to get rid of the ' and make the data how they want it: let them try to find it on the wiki (assumes motivation and a working internet connection). Allow them to waste their time working out how to reenter their data / figure out the correct combination of copy-paste options.

Moving to UNCONFIRMED to get QA team to look at it - very likely it's closed again but looking for additional feedback. Thanks for your patience and understanding.

Nevermind I just reread this - it's not a bug, please don't reopen it. For additional info you can check out this extension: http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

Again - please don't reopen this. Bringing in that extension into core is already reported as an enhancement request.