Bug 37132 - FORMATTING Treat text fields as numbers in formulas
Summary: FORMATTING Treat text fields as numbers in formulas
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: Other All
: medium enhancement
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: target:4.3.0
Keywords:
Depends on:
Blocks:
 
Reported: 2011-05-12 04:43 UTC by Duncan Lithgow
Modified: 2014-03-25 13:24 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Duncan Lithgow 2011-05-12 04:43:11 UTC
Iv'e just received a spreadsheet from a user of excel (2010) and it looked like LibreOffice couldn't read parts of it. It was filled with ### and #VALUE! where I expected values. Turns out that the problem is careless users and different ways of treating cell contents.

* In LO if a cell is formatted as text and contains a number it will cause a failure in calculations
* In Excel (2010) if a cell is formatted as text and contains a number the formular will treat it as a number, despite the formatting saying to expect text. Excel puts a little notification next to the cell offering a message that the formatting might be wrong and offering to change the text to a number.

I think LO should do the same - if a formula finds a cell formatted as text it should check to see if it contains a number, if it does it should be treated as a number (and perhaps offer some kind of warning)

To solve this in a poorly constructed spreadsheet the cells have to be selected and the formatting changed to 'Number'. This changes cells with a number (1.5) to cells formatted for numbers but containing strings ('1.5). These must be manually retyped as numbers. Then the sheet should work if that was the only problem.

This is actually quite important because for a user switching from Excel, or receiving an excel file this will look like a bug. I have given up on many excel spreadsheets in the past which I now suspect have this basic error in their formatting.
Comment 1 Eike Rathke 2011-08-22 17:46:01 UTC
Calc calculates with textual numbers if they represent integer values or ISO 8601 date values. It refuses to calculate with textual numbers if they contain separator characters, e.g. seem to be floating point values, as those are locale dependent and there is no way of telling what would be correct.

Also in Excel you may get calculation errors if, for example, in an en_US locale the textual number is 1.000 and the document is loaded in an de_DE locale, as there the ',' comma is used as a decimal separator and the '.' is the group separator. In older Excel versions the results even differed and gave 1 in en_US and 1000 in de_DE ...

So instead of producing different results in different locales, Calc is stricter in what it accepts in all locales.
Comment 2 Duncan Lithgow 2011-08-24 12:56:08 UTC
Thanks for your explanation Eike. If this is the way Calc users want it then that's fine for ODS files. But ...

When we open an excel file we expect it to work - so we need to find a way to make this just work.

- If we need to ask the user for the locale of the document let's do that
- If we need to add comments on ambiguous cells let's do that
Comment 3 Eike Rathke 2014-03-13 18:52:44 UTC
I'm working on an option to select between different string conversion models during calculation.
Comment 4 Commit Notification 2014-03-14 12:31:13 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c52f3ea0eb327343b1945290c43d3b66f546dfe9

user selectable string conversion models, related fdo#37132 fdo#74622



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 5 Commit Notification 2014-03-14 12:31:27 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=af88ab2ee9167279cb70a577fb399d23f2ce136f

unit test for string conversion feature, related fdo#37132 fdo#74622



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 6 Commit Notification 2014-03-25 13:24:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bc43fc2fb27eab02a949832626ce1a9dc380ee6b

added Formula/Syntax/StringConversion config item, fdo#37132 related



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.