| Summary: | FILEOPEN XLSX TEXT formula gives different result than in Excel | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | NISZ LibreOffice Team <libreoffice> |
| Component: | Calc | Assignee: | Eike Rathke <erack> |
| Status: | VERIFIED FIXED | ||
| Severity: | normal | CC: | erack, jumbo4444, miguelangelrv, nemeth, xiscofauli |
| Priority: | medium | ||
| Version: | Inherited From OOo | ||
| Hardware: | All | ||
| OS: | All | ||
| Whiteboard: | target:7.0.0 | ||
| Crash report or crash signature: | Regression By: | ||
| Bug Depends on: | |||
| Bug Blocks: | 150900 | ||
| Attachments: |
Example file from Excel
Screenshot of the original document side by side in Excel and Calc The problematic formula structure in the Formula Wizard |
||
|
Description
NISZ LibreOffice Team
2020-03-24 14:25:20 UTC
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. |