Apache OpenOffice (AOO) Bugzilla – Issue 3820
Numeric data treated as text (although formatted as numeric)
Last modified: 2013-08-07 15:14:58 UTC
I have formatted cells as numeric by selecting the cells and choosing Format- >Cells...->Numbers Tab, then choosing the "Number" category and any format other than "General". Then when I enter numeric data, it is treated as text. It is left justified and it is not recognized in numeric formulas. For example: In cell A1 enter 123 In cell A2 enter 456 In cell A3 enter =A1+A2 The result is: In cell A1, left justified 123 In cell A2, left justified 456 In cell A3, right justified 0.00 If I precede the numbers in A1 and A2 by an equal sign, then the result is: In cell A1, right justified 123.00 In cell A2, right justified 456.00 In cell A3, right justified 579.00 It may be that there is an option that will treat numbers as numeric without preceding them by an equal sign. If there is such an option, I haven't been able to find it and my searches through the online help have not turned it up. If this is the only data entry option, then in my opinion (as a professional accountant) this is a serious design flaw. I should be able to rapidly enter a column of numbers via the 10-key pad. Most 10-key pads I have seen on PCs do not have an equal sign, which requires the hand to be moved to the regular keyboard for each entry, or it requires two-handed entry.
I'll have a look, Peter
Hi Paul, do you use an localized (i.e. asian) WIN98 version? Or do you have any asain locale support installed ? Works for me so far. Best regards, Peter
*** Issue 3821 has been marked as a duplicate of this issue. ***
Hi Peter, Thanks for looking at this so quickly. I agree that issue 3821 is a duplicate of this. The "text vs. numeric" aspect is common to both. I don't think I'm using a localized version of Win98. In the Control Panel under Regional Settings the setting is "English(Canadian)". I tried changing this to "English(United States)", but that had no affect on the spreadsheet problem. Is there another place I should look to determine whether a Win98 setting is affecting this? Could there be a setting in the options for OO.o that might be causing it? (I went through all the option settings, but I couldn't see anything.) I've changed fonts, but that has no affect. I don't know what else to check. Paul
Hi Paul, I now tried another installation on w98 with english(canadian) locales but it still keeps on working for me. Could you please uninstall OOo, erase the OOo folder and start the installation again. Perhaps something went wrong with the previous setup. Best regards, Peter
Hi Peter, I think I've identified the problem! I did as you suggested-- uninstalled OO.o; deleted the OpenOffice.org641 directory; and reinstalled. Then I opened the spreadsheet and typed in numeric data and dates, etc. (see above and issue 3821). Everything worked great, as it is supposed to. Then I went to Tools->Options... in order to set my preferences as I like them for each module. After doing that, I came back to the spreadsheet and found it broken again!! So I knew the problem is in some option that I changed. Here it is: Choose Tools->Options.... Go to Language Settings->Writing Aids. Then in the Options section put a check mark in "Check words with numbers". That will break the spreadsheet as I initially described in this issue and in 3821. Obviously, I can "fix" the spreadsheet by unchecking this option (which I will do for now), but I do like the spell checker to check words with numbers in my word processing documents. Is there any way to have it turned on for one module (like Write or Impress) but off for others (like Calc)? Paul
HI Paul, I'll check this again. NO, these options work globally. I think this makes sence due to options may get extremly confusing when everything is set for every application individually. Best regards, Peter
Hi Paul, still not reproducible. Maybe the issue appears only in combination with one of the other settings you changed. Please list everything option you touched. Due to this happens only with certain settings and not in general I decrease the priority to normal level. Best regards, Peter BTW: Thanks for this good evaluation. IMHO we will nail this nasty bug together.
Hi Peter, Thanks for your patient working with me on this. I couldn't remember everything I changed in the Options, so (rather than doing a full uninstall/re-install) I deleted the share\config and the user\config files. Then I ran Setup and chose the "Repair" option. I presume that the new config files that it gave me reset everything to "out of the box" condition. I opened a spreadsheet. It worked as it should. I then went to Tools- >Options... and then Language Settings->Writing Aids. Then in the Options section put a check mark in "Check words with numbers" _and_ in "AutoCheck". With just those changes to Options I was able to reproduce the problem. Hopefully that's pinpointed it! Paul
BINGO! ;-)
OK, just one more time how to reproduce (for all who lost overview): -install OOo641d -check tools->options->language->writing aids-> a. check words with numbers b. Autocheck -enter numeric values (here comes the clou) with more than one decimal places before or behind the delimiter -> these numbers are formated as text i.e. 9.9 is recognized as number - 9.99 and 99.9 as regarded as text This is a unique OOo issue. No problem in StarOffice. Best regards, Peter
Hi Niklas, looks like the external spellcheck or the different framework cause trouble in cooperation with the number formatter. I hope you know who's responsible. Best regards, Peter
*** Issue 3717 has been marked as a duplicate of this issue. ***
*** Issue 5599 has been marked as a duplicate of this issue. ***
Text with online spelling errors is treated as formatted text, which is always stored as such, regardless of its content. We'll probably have to change this and test for valid numbers even in formatted text.
*** Issue 8818 has been marked as a duplicate of this issue. ***
*** Issue 6767 has been marked as a duplicate of this issue. ***
*** Issue 10985 has been marked as a duplicate of this issue. ***
Numerical input with spelling errors is now treated as number, and the spelling errors ignored. The fix is in sc/source/ui/app/inputhdl.cxx 1.44.2.1.2.2, in calc03 child workspace. It will also be in a future 644 build.
Reopening to change owner.
Reassigning to QA for verification.
Restoring "fixed" state.
Verified in cws_calc03 (will be available as later OOo644)
As mentioned on the qa dev list on March 5th I will close all resolved <wontfix/duplicate/worksforme/invalid> issues. Please see this posting for details.
*** Issue 18214 has been marked as a duplicate of this issue. ***
I would like to suggest that Issue 10893 http://www.openoffice.org/issues/show_bug.cgi?id=10893 is a duplicate of this one.