Contact
12 programs
Explore the site

COMPARE in Calligra Sheets: Case-Sensitive and Case-Insensitive Text Ordering

Text Intermediate Calligra Sheets

Compare two text values in Calligra Sheets using a required case-sensitivity flag; understand -1, 0, 1 and locale-aware ordering.

Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Calligra Sheets. How formula examples are checked

Quick answer: =COMPARE(A2;B2;FALSE) compares two text values without distinguishing upper- and lower-case letters. It returns 0 for equal text, -1 when the first sorts before the second, and 1 when the first sorts after the second. Supply TRUE as the third argument to request a case-sensitive comparison. This is a number indicating ordering, not a TRUE/FALSE equality test.

Verification disclosure: This guide targets the official Calligra 26.08.2 source release of October 8, 2026, consulted 2026-10-11. Syntax and implementation were checked against KDE’s official function documentation and the version-pinned code. Results below are either independently reasoned or documentation-stated expected results; no formula was executed in a running Calligra Sheets installation. No genuine application screenshots are claimed.

When to use COMPARE

Use COMPARE when you need an ordering result for two values, for example to categorize whether an incoming identifier matches a stored one, falls before it or after it, or differs only in letter case. COMPARE is different from EXACT, which answers a yes/no question rather than producing -1/0/1. Because sorting is locale-aware, do not use numeric COMPARE results as a stable substitute for byte-wise identifiers, digital signatures or universal Unicode sorting.

Exact official syntax and argument order

=COMPARE(string1;string2;true|false)
Position Argument Required? Type in KDE reference Meaning Default
1 string1 Yes String First text value None
2 string2 Yes String Text to compare against the first None
3 `true false` Yes Boolean TRUE distinguishes case; FALSE ignores it before ordering

The registered function in text.cpp lines 109–111 requires exactly three arguments. The implementation in func_compare, lines 224–243 converts both values to text, lowercases both values when the third argument is false, invokes Qt localeAwareCompare, then normalizes the result to -1/0/1. The KDE XML help entry documents COMPARE(string1; string2; true|false) with three required parameters. None is optional.

Locale and platform: The English KDE guide uses semicolons in multi-argument formulas. Confirm separators and localized function names in the formula editor of the installed edition. Case conversion and Qt collation depend on locale and Unicode implementation; the exact order of differently accented or differently capitalized words may vary. Documentation and pinned code cover the shipped function, not a native check of a particular Linux package, Flatpak or Windows installation.

Reproducible worksheet: compare imported names

  1. In A1, enter Submitted name; in B1, enter Master name; in C1, enter Ignore-case comparison; in D1, enter Exact-case ordering.
  2. Enter North in A2 and north in B2; pear in A3 and PEAR in B3; same in both A4 and B4.
  3. Enter =COMPARE(A2;B2;FALSE) in C2 and =COMPARE(A2;B2;TRUE) in D2. Copy the patterns down.
  4. Expected C2, C3 and C4: 0. For row 2, D2 is expected nonzero under a case-distinguishing collation; the actual result and sign are locale-dependent. Use EXACT when character equality rather than collation equivalence matters. For row 4, D4 is 0.
Formula Expected value Validation and interpretation
=COMPARE("North";"North";TRUE) 0 Same characters; independent reasoning.
=COMPARE("North";"north";FALSE) 0 Lowercased before comparison; independent reasoning.
=COMPARE("pear";"PEAR";FALSE) 0 Same after lowercasing; independent reasoning.
=COMPARE("";"";TRUE) 0 Both inputs empty; independent reasoning.
=COMPARE("calligra";"Calligra";TRUE) 1 in KDE’s documented example Documentation-stated result; not guaranteed in every locale.
=COMPARE("kspread";"Calligra";FALSE) 1 in KDE’s documented example Documentation-stated result; locale may change collation.
=COMPARE("ant";"zebra";TRUE) -1 under typical English alphabetical collation Independent English ordering assumption; verify actual locale.

A numeric cell format displays the return value. COMPARE is not a cell-formatting or sorting command, and does not modify A or B.

Advanced usage: categorize rather than just test equality

Assume A2 is the submitted value and B2 is the stored value. Put this formula in C2:

=IF(COMPARE(A2;B2;FALSE)=0;"Equivalent ignoring case";"Review different values")

For A2=North, B2=north, the expected result is Equivalent ignoring case. Change B2 to South and the expected result is Review different values. The logic tests equality only; it does not depend on locale-sensitive ordering signs. IF and COMPARE are registered Calligra functions; this combined formula is reasoned from their documented meanings, not runtime-observed.

For a case-sensitive equality check, prefer =EXACT(A2;B2) if you only need TRUE/FALSE; it also avoids treating a numeric -1 as a Boolean truth value. For multilingual sorting decisions, perform a native locale test before relying on the sign of a nonzero COMPARE result.

Errors, limitations and troubleshooting

  • “Argument missing” or wrong argument count: All three parameters are mandatory. =COMPARE(A2;B2) is incomplete; there is no documented default for case-sensitivity.
  • Unexpected 0: When the third parameter is FALSE, a capitalization difference alone disappears before comparison. Use TRUE or EXACT to distinguish capitalization.
  • Different -1/+1 signs on another device: Qt uses locale-aware comparison. Your language, collation rules and version affect order; for robust identifiers, consider a case-sensitive equality test instead of interpreting ordering.
  • Values are numeric or dates: The pinned code converts inputs to strings first. Check locale-dependent text conversion before comparing formatted numbers, date serials or values imported from CSV.
  • Separator or Boolean name rejected: The official English documentation writes semicolons and true|false for the third argument; confirm the installed locale’s input convention.
  • Unicode normalization: This guide does not claim that visually identical composed/decomposed Unicode strings compare identically. Test the exact text and locale when international data matters.
  • Errors or limits not specified: No arbitrary Calligra-specific string-length limit or universal error text is inferred from the cited registration. Native error messages and unsupported input types remain untested.

Cross-compatibility with Excel and LibreOffice

In Excel, EXACT is an equality-only Boolean function. Excel’s comparison operators can order text but are not a drop-in equivalent of Calligra’s COMPARE(string1;string2;case_sensitive) numeric -1/0/+1 result and its explicit case flag. Do not paste a Calligra COMPARE formula into Excel and assume identical function availability or locale behavior. For LibreOffice, verify the target function and comparison rules rather than assuming an equivalent three-argument function. Exchange only the underlying purpose and test conversions in the target application.

Use the official KDE pages for EXACT, LOWER, UPPER, IF, and SEARCH. These links point to official documentation.

Official reference and verification (2026-10-11; release 26.08.2)

Validation status: Official documentation and version-pinned source checked; specified example outcomes are either documentation-stated or independently reasoned under the indicated locale assumptions. Not executed in Calligra Sheets. No genuine Calligra screenshots are included.