Apache OpenOffice (AOO) Bugzilla – Issue 126754
Text function not working when combined with Left
Last modified: 2016-01-01 16:48:58 UTC
When I try textvalues of 7a-z in 1 row and the following function in the second row: 'LEFT(TEXT(Cell,1))', I get these results: (this is a depiction of my file in OOCalc) row 1 row 2 7a 7 7b 7 7c 7 7d 7 7e 1 7f 7 7g 7 7h 7 7l 7 7j 7 7k 7 7l 7 7m 7 7n 7 7o 7 7p 7 7q 7 7r 7 7s 7 7t 7 7u 7 7v 7 7w 7 7x 7 7y 7 7z 7 Note how only '7e' is different from all the others. How can this be? This is very troublesome when working with hexadecimal :(
What you describe does not make sense. - You write row1, row2 but you mean column A, column B - Since cells in column A contain a text, there is no need to use the TEXT() function. - The arguments of a function are separated by ; instead of a , - Function LEFT() has two arguments, you write only one. You should ask for advice in our support forum : https://forum.openoffice.org
Created attachment 85224 [details] The problem in a small file, from Calc v.4.1.2
My sincere apologies for the sloppy description in the first post. The problem is that I want to use the text function because I need to use values like '01' and '00' etc.. When a value has a number followed by character 'e', the formula does not behave as expected.
Read comment 1 and post directly this kind of question on the support forum.
Sorry, oooforum. I see it as a misbehaviour that Calc tries text to read as numbers. This was introduced to do it like Excel, but now Calc is defective in the same way. But the TEXT() function gives also wrong results (in my opinion): TEXT("3a","0") = "3a" The result should be 0, because text has per definition the value 0! TEXT("3e","0") = "3" It should be also 0, but it is "3" because "3e" is interpreted as the value 3e0 = 3E+0 = 3*(10^0) = 3
That makes sense, mroe. Thank you. I will have to look for workarounds then :/. But is this a bug or not? I think it is.
(In reply to warenhuis1 from comment #6) > That makes sense, mroe. Thank you. I will have to look for workarounds then > :/. But is this a bug or not? I think it is. In addition, the user 'Lupp' has made the following conclusions after viewing my testfile. I will also post it here for others to see: by Lupp » Tue Dec 29, 2015 1:44 am 0. @warenhuis : As long as your HEX numbers are text by type, no suppression of leading zeros will occur. General (and possibly important): 1. There is clearly a bug, not just a problem to be solved (worked around) by users. 2. Using AOO4.1.2 I do not get the result "1" for "=LEFT(TEXT("7E";"00");1)" as "warenhuis" stated, but "0" (both these digits being text). I cannot understand the "1" in any way. 3. The error is caused by the application of an incorrect automatic conversion of a text like "7E" into a number, and independent of the subsequent application of TEXT (or LEFT after that). 4. The most reduced appearence of the bug is CODE: SELECT ALL EXPAND VIEW =0+"7E" returning the number 7. Starting from that the other buggy behaviour mentioned is fully consequent. 5. The background "mroe" is guessing in the discussion of the mentioned bug may or may not be correct. It should not be relevant, however. In accordance with the general rules for implicit conversion the TEXT function (or the evaluator preprocessing the parameters?) shall be implemented in a way also accepting a numeric text on the numeric parameter position. It has to do it by a correct automatic conversion of that text into a number, of course. 6. The complete syntax for constant numbers as specified in the only mandatory document http://docs.oasis-open.org/office/v1.2/ ... -part2.odt is: CODE: SELECT ALL EXPAND VIEW 5.3 Constant Numbers Constant numbers are written using '.' (FULL STOP, U+002E) dot as the decimal separator. Optional "E" or "e" denotes scientific notation. Syntactically, negative numbers are positive numbers with a prefix "-" (HYPHEN-MINUS, U+002D) operator. A constant numberis of type Number. Number ::= StandardNumber | '.' [0-9]+ ([eE] [-+]? [0-9]+)? StandardNumber ::= [0-9]+ ('.' [0-9]+)? ([eE] [-+]? [0-9]+)? Evaluators should be able to read the Number format, which accepts a decimal fraction that starts with decimal point '.' (FULL STOP, U+002E), without a leading zero. Evaluators shall write numbers only using the StandardNumber format, which requires a leading digit, and shall not write numbers with a leading '.' (FULL STOP, U+002E). This does obviously not allow for an empty exponent (resulting in taking "7E" for "7E0"). This is how numbers are to be represented in the file (persistent). Since there is nothing said in addition about numbers regarding the UI, the above specification should also be applicable on that level. This with the exception of details overlayed by the locale. No such exception relevant for the case under discussion here! 7. I am not known to the AOO bugzilla site. Might someone else care for the conclusions from this post to be also posted in the mentioned bug report, please? 8. LibO V5.0.4 does not suffer from the bug. Original link: https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=81032
I've never used the TEXT function in Calc but here is the definition from: http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018004_715980110 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 6.20.23 TEXT Summary: Return the value converted to a text. Syntax: TEXT( Scalar X ; Text FormatCode ) Returns: Text Constraints: The FormatCode is a sequence of characters with an implementation-defined meaning. Semantics: Converts the value X to a Text according to the rules of a number format code passed as FormatCode and returns it. See also N 6.13.26, T 6.20.22 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< This definition could be quite different from what is defined in Excel.
(In reply to Kay from comment #8) > I've never used the TEXT function in Calc but here is the definition from: > > http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2. > html#__RefHeading__1018004_715980110 > > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > 6.20.23 TEXT > > Summary: Return the value converted to a text. > > Syntax: TEXT( Scalar X ; Text FormatCode ) > > Returns: Text > > Constraints: The FormatCode is a sequence of characters with an > implementation-defined meaning. > > Semantics: Converts the value X to a Text according to the rules of a number > format code passed as FormatCode and returns it. > > See also N 6.13.26, T 6.20.22 > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< > > This definition could be quite different from what is defined in Excel. Also, the definition of Scalar for OpenFormula is any single value of one of the scalar types Number (including complex), Logical, and Text. Since FormatCode is implementation-defined, we need to look at what those definitions are for Apache OpenOffice. (The implementation is required to have a specification for that definition.) I expect that some harmony with Excel is intended but it is not clear what the extent of it is, nor how a Text value is handled when the FormatCode applies to Number values although the definition for the FormatCode could decide the matter.
The behavior, and the apparent treatment of TEXT("07e"; formatcode) as TEXT(7e0; formatcode) is a matter for clarification in the definition of the formatcode strings for Apache OpenOffice. This may be a documentation defect or it may be that the behavior is not as it was intended to be. This will have to be explored more deeply and reconciled with the OpenFormula specification and documentation of the feature for Apache OpenOffice. There are also interoperability concerns to address.
As I was quoted (I am 'Lupp' in the forum.openoffice.org), I want to also post my actual view of this. The issue is not at all related to any application of TEXT and/or LEFT and to the bahaviour of these functions. They do their job as they are specified to do. The issue is exclusively one of implicit conversion from text into number in a very specific case. Let me explain: "1E0" in the position of an operand of an arithmetic expression will implicitly convert into the number 1. The same if placed on a parameter position specified to accept a number. This is correct. The one (many) in charge of implementing the implicit conversion did it in a way allowing for an empty exponent when given a number in "scientific notation", and interpreting this missing of an exponent as a 0 intended. Thus "1E", "1E+", "1E-" are treated equivalent to "1E0" on conversion. As there is no explicit specification about how numbers to enter/import or to be created by conversion from text in Calc should look, we have to resort to the specification of numbers for the persistent representation, and apply it with the least possible modifications needed under any specific locale. In any case we should not accept empty exponents for the scientific notation. Another approach may be that the implicit conversion should, as near as possible, accept by the same syntax as the explicit conversion by the VALUE function and the recognition routine to apply to edited cells do. Finally even the implicit conversion as is, does not accept an empty exponent (for 0) if applied on a number in scientific notation when a decimal fraction part is included. This very inconsistent behaviour should be cleaned out. LibreOffice does not behave this way (at least since V3.5.2; it still did in V3.3). The interoperability can surely not be re-established by LibO taking over the inconsistencies. I did not test myself but was told that even Excel is sound with this respect.