For a long time localization (“l10n”) affected formulas only in two ways:
- The function names. (Users interested in international cooperation chose ‘Use English function names’ therefore.)
- The decimal separator used in numeric constants. (Just a plague since ISO accepted the alternatives, no simple workaround I would know of.)
A long time ago, l10n spillt over concerning now also the parameter delimiter: In locales not using the comma as the decimal separator, the comma replaced the semicolon as the default parameter delimiter for functions. Very bad. Lots of formulas, also those not containing numeric constants, which formerly were exchangeable globally, are no longer, therefore.
What to do?
Generally set ‘Use English function names’ under ‘Tools’ > ‘Options’ > ‘LibreOffice Calc’ > ‘Formula’ > ‘Formula Options’.
You will see that the few English terms you need to know then aren’t a problem at all. You may also use an English UI-language to be better prepared for international cooperation. (I do so despite being a German in Germany.)
Generally enter a semicolon into the control right of ‘Tools’ > ‘Options’ > ‘LibreOffice Calc’ > ‘Formula’ > ‘Separators’ > ‘Function:’.
Your Calc will then always display formulas using this parameter separator, and you can copy these formulas for global export without causing problems for those importing the formulas into their sheets. The semicolon is still accepted even if the locale uses the comma. Automatic conversion should take place for the view. Please, please, please, developers, don’t change that!
The persistent format [file] still uses the semicolon anyway here due to mandatory specification and so does the
.Formula property of cells accessible via the API from user code.
You want to paste a formula and get the problem:
Thoroughly rectify the formula manually in the appropriate way with respect to your UI and locale settings.
(If complicated / frequent:) Paste the formulas as text first and rework them with the help of
Find & Replace.
You want to post a formula for use/testing" by others:
Accept the advice above concerning English function names and overriding the comma as the parameter delimiter by a semicolon if needed. No specific measures needed then in any single case - except, probably, a hint for readers to regard the decimal separator if non-integer numeric constants are contained.
A few final caveats
- Localization beyond the absolutely necessary is a way to hell concerning cooperation. It should be abandoned.
- Never use USA date formats except for helping to get rid of them. Always use 4-digit-year in dates.
- Do not believe the point is the “international” decimal separator. The majority of locales uses the comma in this role.