Created attachment 158936 [details] Example file from Excel Attached document contains a TEXT formula that was made in Excel. If it is called using the empty string as third parameter, embedded in an IF formula, it gives back a Err:502 result, unlike Excel. However if the same formula is called using “0” as third parameter, the error is not present. The problem does not appear until the result from TEXT is used in an IF formula, so the example file contains a breakdown of the complex IF formula as well. Steps to reproduce: 1. Open attached document. Actual results: The formula in D10 gives TRUE in Excel, but Err:502 in Calc. The same formula with a small change in the TEXT formula is in E10, gives 1 in Calc. Expected results: The formula in D10 gives 1 in Calc. LibreOffice details: Version: 7.0.0.0.alpha0+ (x64) Build ID: b07d429f8610c4df93dffa72552f4f759ec0b74f CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win; Locale: hu-HU (hu_HU); UI-Language: en-US Calc: CL Also in: Verzió: 6.0.0.3 Build az.: 64a0f66915f38c6217de274f0aa8e15618924765 CPU szálak: 4; OS: Windows 6.3; Felületmegjelenítés: GL; Területi beállítások: hu-HU (hu_HU); Calc: CL Version: 5.0.0.5 Build ID: 1b1a90865e348b492231e1c451437d7a15bb262b Locale: hu-HU (hu_HU) Version: 4.3.0.4 Build ID: 62ad5818884a2fc2e5780dd45466868d41009ec0 In 4.0 - 4.2 even worse, since the Err:502 appears as result of the simpler formulas, not only IF.
Created attachment 158937 [details] Screenshot of the original document side by side in Excel and Calc
Created attachment 158938 [details] The problematic formula structure in the Formula Wizard
Reproduced in Version: 7.0.0.0.alpha0+ Build ID: 9163755e9f64a0b1dd5f2090e0702c19e31c12c9 CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded
At least in calc TEXT() it's not need for the formula. Not sure empty string it's a valid option for TEXT() function. https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.pdf 6.20.23 TEXT Summary: Return the value converted to a text. Syntax: TEXT( ScalarX ; TextFormatCode ) Returns: TextConstraints: 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.
An empty format string makes no sense as it would return an empty string for any input value. If that is actually desired the proper format string would be ";;;" (i.e. all empty sub formats). However, if Excel allows such in TEXT() we could as well. ODFF puts no constraints on TextFormatCode. BUT, what does Excel actually do there? Apparently it does not return an empty string? Does it return a formatted string according to the 'General' format? Something else? Can someone please check? For example, output for these is ...? =TEXT(0;"") =TEXT(12.3456;"") =TEXT(1E23;"") =TEXT("lala";"")
Tests with Excel 2010 (In reply to Eike Rathke from comment #5) > For example, output for these is ...? > =TEXT(0;"") returns empty string > =TEXT(12.3456;"") returns empty string > =TEXT(1E23;"") returns empty string > =TEXT("lala";"") returns "lala" if argument is numeric (date, time, percent, scientific...) => empty string if argument is text => string with same text /!\ if text can be converted as a value, empty string is returned: =TEXT( "5:3" ; "" ) or =TEXT( "5e3" ; "" ) returns empty string
Thanks Laurent! As usual for Excel, I assume the conversion from string to numeric to have the awkward behaviour of being locale dependent, thus indeterminate. Taking.
(In reply to Eike Rathke from comment #7) > As usual for Excel, I assume the conversion from string to numeric to have > the awkward behaviour of being locale dependent, thus indeterminate. As usual, you are right ;-) In French environment, Win7, Excel 2010: =TEXT( "5 mai" ; "" ) returns empty string, as "5 mai" is detected as a date, while =TEXT( "5 May" ; "" ) returns "5 May", as English date is not detected. Good luck
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b7a53077fd592c514d2f255cc19e4f0deb483dca Resolves: tdf#131536 TEXT() support empty format string mimicking Excel It will be available in 7.0.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Verified in Version: 7.0.0.0.alpha0+ Build ID: 1eb6210d678bbb0c875f0f72b... CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded @Eike, thanks for fixing this issue!!
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/be29bd29864e450b0a99898d560ba71562cf06da tdf#131536: sc: Add unittest It will be available in 7.0.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.