LibreOffice 4.3 new Calc feature: user selectable text conversion models
In LibreOffice 4.3 the user will be able to select how textual cell content shall be treated in arithmetic operations.
This setting determines how to treat text when encountered as operand in an arithmetic operation or as argument to a function that expects a number instead.
Selectable under Tools → Options → LibreOffice Calc → Formula in Detailed calculation settings → Custom → Details from Conversion from text to number are:
- Generate #VALUE! error
- =1+"1" or =1+"x" give #VALUE!
- Treat as zero
- =1+"1" or =1+"x" give 1
- Convert only unambiguous
- =1+"1" gives 2, but =1+"1.000" or =1+"x" give #VALUE!
- Convert also locale dependent
- =1+"1.000" may be 2 or 1001 ... =1+"x" gives #VALUE!
For Generate #VALUE! error and Treat as zero, setting the Treat empty string as zero option is disabled and the option is aligned to these settings, for Convert only unambiguous and Convert also locale dependent the Treat empty string as zero option can be set independently.
When reading documents created by other spreadsheet applications or older versions of LibreOffice, and to interchange documents between different locales, the Convert only unambiguous with Treat empty string as zero = True setting is recommended, though LibreOffice so far acted as Convert also locale dependent with Treat empty string as zero = False, which is the reason that option is kept as default.
The best setting to create new documents that can be interpreted by all spreadsheet applications without on-the-fly string conversion is Generate #VALUE! error. With this option enabled possible data errors can be spotted early. Not having to convert strings during calculation is also faster, of course.