Bug 75462

Summary:	Spreadsheet throws an error if TEXT() parameter is not a number.
Product:	LibreOffice	Reporter:	AlanH <bugzilla>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED NOTABUG
Severity:	enhancement	CC:	miguelangelrv
Priority:	medium
Version:	4.1.5.3 release
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	.xls file with TEXT() formula

Description AlanH 2014-02-24 18:39:30 UTC

Open an Excel spreadsheet that includes a formula containing =TEXT(<cell-ref>, "") where cell-ref refers to a cell containing text only. Result is Err 502. Excel allows text-only parameters in the TEXT() formula.

Comment 1 m_a_riosv 2014-02-24 22:38:49 UTC

Hi AlanH, thanks for reporting.

Please can you attach a sample file, for verification, seems works fine in 4.2.1 but not in 4.1.6.

Comment 2 AlanH 2014-02-25 10:30:25 UTC

Created attachment 94710 [details]
.xls file with TEXT() formula

Comment 3 AlanH 2014-02-25 10:32:46 UTC

I was testing with 4.q.5, as that seemed to be the latest version. I have now downloaded 4.2.1 and the attached file still displays the same error message on my Mac. It works in Excel 2003 in WindowsXP

Comment 4 m_a_riosv 2014-03-01 03:46:29 UTC

I though there was an space as second parameter, but in your sample file it is an empty parameter.

The error is: invalid argument.
=TEXT(A1;"")
There is nothing inside double quotes, no format to show.
I don't know how excel does. (I haven't it).

Please what is the target to use the formula in this way?

This behaviour has been always the same in all versions of LibreOffice, it was never changed.

Changed the status to resolved notabug, please if you are not agree reopen it.

Comment 5 AlanH 2014-03-01 10:25:23 UTC

I am a volunteer treasurer for a local not-for-profit association. I have to complete a report template each year to summarise my accounts for submission to the organisation headquarters. The template is a protected Excel document that uses this formula to create a text document based on my raw data. It has no space character.

The template protection means I cannot edit the formula to make it compatible with LibreOffice. I have asked headquarters to change it to make it compatible. I told them they do not need to use the TEXT() formula in this case, but I do not know whether they will do it.

I do not own Excel, and hoped to use LibreOffice to complete this task. This year I have had to use someone else's copy of Excel. I have already donated to LibreOffice, and I would rather not buy MS Office just for this report in future years.

Since this is an incompatibility between Excel and LibreOffice, I thought I should report it as an enhancement request. If you do not want to make it compatible, that is your decision, and you should change the status to "WONTFIX", but it seems a pity. I shall have to hope that headquarters will change their formula.
 
I've reopened it in case you want to take it further.

Comment 6 m_a_riosv 2014-03-01 13:11:32 UTC

Hi AlanH, people here are volunteers, in my case helping to triage bugs, developers are free about what enhancements implement. Not usual see a won't fix.

Compatibility sometimes is an arguable question, in this case is not clear for me that an empty parameter must work. Perhaps the error is en excel.

Seems more reasonable one organization arranging a formula that they don't use, than a project changing a long term status for everybody, without know what issues can result for other users.

Comment 7 AlanH 2014-03-01 17:17:02 UTC

Thanks. I can't find a reference that states whether the format string can be empty in Excel, and I can't see a need for this option. So I guess you are right. 

However, if a user does make this error, Excel provides a more graceful result than LibreOffice, and I imagine LibreOffice would like to match Excel if possible.

I'll try again to get headquarters to change their template.