Apache OpenOffice (AOO) Bugzilla – Issue 23348
when changing cell type from text to number ' is inserted in front of numbers
Last modified: 2013-09-12 23:07:47 UTC
sometimes (usually in excel prepared documents) there are cells that contain numbers and are formatted as text. in calc these cells are not considered as numbers when performing calculations. now, when the type is changed to number calc inserts ' (apostrophe) in front of number, thus it is still stored as text and not used in calculations. sometimes there are a lot of cells that have wrong formatting and fixing this is extremly hard. possible solutions (that could be combined) : 1. don't insert ' when changing type; 2. when opening .xls document that contains numbers formatted as text (and maybe included in calculations) ask if they should be converted tu numbers (could be very cpy consuming to analyze document and probably unnecessary); 3. add some option to choose between inserting ' and not inserting it. probably 3rd possibility would be the best - for example, in cell properties when changing cell type to number having an option to convert numbers to "number" would be enough
Hi Bettina, 1 4 u. Frank @richlv To convert such numbers from text to number, use Search&Replace and '.*' as search string and '&' as replace string (both without quotes). Make sure you have tagged the Regular Expression checkbox at the Dialog. Frank
OpenOffice.org Issue Tracker - Feedback Request. The Issue you raised has the status 'New' pending further action, but has not been updated within the last 4 years. Please consider re-testing with one of the latest versions of OOo, as the problem(s) may have already been addressed. Either use the recent stable version: http://download.openoffice.org/index.html or consider trying the new OOo 3 BETA (still in testing): http://download.openoffice.org/3.0beta/ Please report back the outcome so this Issue may be Closed or Progressed as necessary - otherwise it may be Resolved as Invalid in the future. You may also wish to search for (and note) any duplicates of this Issue that may have advanced further by checking the Issue Tracker: http://www.openoffice.org/issues/query.cgi Many thanks, Andrew Cleaning-up and Closing old Issues as part of: ~ The Grand Bug Squash, pre v3 ~ http://marketing.openoffice.org/3.0/announcementbeta.html
Issue still exists in OOo 3.0.1 and 3.1 Does it make sense to insert an apostroph if - the cell content is a number - the user explicitly selects the format change from text to number? If I change the formatting from text to number, I would expect a number not a text
To grep the issues easier via "requirements" I put the issues currently lying on my owner to the owner "requirements".
Created attachment 71224 [details] Testcase where user will be confused after inserting a line
Though it makes sense for the strong typing point of view, it is confusing for the user. I have a recurring case where I insert a line into a spreadsheet and a cell which I use as number becomes text. Then, if I type a number there, and then find out it's not a number, I'll change its format. But then the number is rendered useless by the apostrophe. Plus: since the apostrophe isn't shown, I won't tell what's wrong until I click on the cell. Even more: searching apostrophes doesn't work as an end user expects (one should know about the '.*' and '&' thing, and this is another issue). That is, a regular user would get lost (as I got). Pls see my last attachment. Cell B2 is number and cell B1 is text. If you insert a line over line 2, cell B2 will become text. It's reasonable, since Calc has to copy format from either the upper or the lower line, but the user doesn't have a clue yet. So, when the user tries to use cell B2 as a number, the battle begins. If the user inserts multiple lines, it gets worse - which is my case. So, IMHO, Calc should either accept a number in a number-formatted cell as a number or tell the user it'll do otherwise and offer an easy way out. In the current way, it only makes sense to geeks :) . Pls help normal users. Best regards
This problem still exists in AOO 3.4. A prompt asking if the apostrophe should be stripped from the column would be fine. And/or an easier way to remove the apostrophe (in a selected area) than using the search and replace using the regular expression would be very much appreciated. As someone mentioned, that method is fine for geeks, but not regular people. This would probably solve issues 29891, 60110, 68652, 65510, 69067, 23348, 117470, and any others that are similar as well.
There is an extension that strips the apostrophes from the selected area or optionally the whole sheet from text fields (at least I do not think it will work in number fields) called Text to Number and Date. It works well although in 4.0 it no longer appears on the tool bar. If this feature were built-in to AOO that would be nice. Although, logically, if you are convert text to numbers then you should *get* numbers not numbers marked as text. Otherwise, it makes no sense to bother changing the field.