Skip to content

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 settingsCustomDetails 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.

User selectable text conversion models in LibreOffice 4.3



No Trackbacks


Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.

You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Form options