Apache OpenOffice (AOO) Bugzilla – Issue 57095
Allow to change Cell format for "Text-" Numbers from text to number
Last modified: 2008-02-20 15:28:57 UTC
i am using ooo 1.9.129 on ubuntu 5.10. 1. create spreadsheet. 2. change the format of all the cell to text. 3. enter some values in some cells like 100 or 200 etc. 4. now select those cells and then change the format back to number. use the given format 1234 actual result - the format of cells is still text. user cannot do any summation, or any numeric funtion on the cells. i entered 150 values. it was complete waste of time to remove the ' that was present before the values.
I checked with 2.0 German version WIN XP: [680m3(Build8968)] and can confirm that problem. Same with 1.1.4 (German) WIN XP: [645m52 (Build 8824)]. This is known from issue 24374, where you will find a workaround. We shoud think about that WONTFIX again, what imho causes a very user unfriendly behaviour of OOo. Can anybody tell us a good reason for that funny behaviour in OOo?
Hi, this *is* a double to Issue 24374 . And it's not unfriendly. Think about a sheet formatted as text one of these cells is referenced on an other sheet with an vlookup based on textdata. Now you format the referenced cell as number and the text changes to that. As a result the vlookup does not solve correctly and you get in trouble. This is the fact that makes this Issue a wontfix Issue. Also the conversion of such values to a real number is easy by using find&replace with regular expressions. Frank *** This issue has been marked as a duplicate of 24374 ***
closed double and wontfix
Hi, how does one use "find&replace with regular expressions"? how will the common person know abt this? if there are 2 cells A1 having '100 and A2 haing '200, and now I want to change only A1 from text to number, how to do it? note that I dont want to change the A2 column formatting. dont give some technical reasons for wontfix. i didnt know that the volunteer programmers would be having the same attitude of the pay programmers of my comapny! ~A.
To get rid of the apostrohe just use Find & Replace search for '.*' replace with '&' and set regular expression under More in the Dialog.
Hm, of course, that example in comments from fst Tue Nov 1 00:50:21 -0800 2005 hits, but the question is how often that OOo behaviour will save users from _that_ problem and how often users will run into problems because they never heard of that "search and replace trick". Of course, there are many other possibilities to get that "text numbers" with "'" to "real numbers" again, for example you can cut, change cell format to "number" and paste special as "unformatted text" again ... Is there anyone who can contribute some document where this special behaviour and the difference between - Numbers inserted into text formatted tells, which become that ' in front of them if you try to change them to format "Number" and what can not be used for calculations - Numbers formatted to "Text" after they had been inserted, what can be rechanged to format "number" without problem and what can be used for calculations even during being in text format without problem? Thanks Rainer
"heard of that "search and replace trick". Of course, there are many other possibilities to get that "text numbers" with "'" to "real numbers" again, for example you can cut, change cell format to "number" and paste special as "unformatted text" again..." I downloaded OOO and was using it. This is a real world scenario, wherein I was entering my financial details and hit this WONTFIX of ooo. This occured in 1st hour of my usage of OOO. There would be many such cases of various people. Please try to understand that these are genuine problems of real world people - who are least interested in the documentation or the technical details. Consider a huge data size. Your cut paste solution wont work in a real world senario. Also, I have seen people use excel with minimum size of 5 to 10 MB. OOO guys cannot consider people to do a cut/paste or anyother workaround. We need to take MS Office headone to succeed. What is the bahaviour in latest staroffice? This weekend I will check the working on the spreadsheet provided by kubuntu. You can also try it and let us all know how does this particular case work. I have heard that various comapnies have made MS Office an OS in itself and have created a strong ecosystem around it. People are selling financial charts and formulae, legal stuff all based on MS Office. I hope that you understand what I am trying to explain. "Now you format the referenced cell as number and the text changes to that. As a result the vlookup does not solve correctly and you get in trouble." A simple regexp will help you solve whether to keep - or to be precise - "consider" the cell contents as text or as numbers. Forget the "vlookup". Consider this simple case in excel (this simple case would be applicable to OOO as well --> In excel 2000 enter 123 in cell A1 and abc in cell A2. Both of them are general. Now change the A1 to text and A2 to number and check the cell contents. Using ' in front of numbers was an old paradigm. Each cell's datastructure will have a member variable called format. If format = text, using text rules, if format = number and regexpression shows that its a number, use the cell as number - else use it as a text - or any other format that its forced into.
Some other "workaround", a macro changing "real text numbers" to "looking as text numbers", you fin here: <http://www.winnirohr.de/ooo/makros/calc_Txt2Num.sxc> "Regina Herschel" told me a source where you can read about all those formatting issues: <http://specs.openoffice.org/calc/ease-of-use/enhance_number_recognition.sxw>
Hi, this is a double to Issue 24734 and closely related to Issue 5658. So I close it again as double. Frank *** This issue has been marked as a duplicate of 24734 ***
closed double
fst: I think you got the issue number wrong. Issue 24734 could not be the original issue of this one.
Yes, mixed up the numbers. It's a double to the before mentioned Issue 27374 and I close it again as such. Frank *** This issue has been marked as a duplicate of 24374 ***