Contact
12 programs
Explore the site

SEARCH in Calligra Sheets: Find Text Without Matching Case

Text Intermediate Calligra Sheets

Find a text pattern inside another string in Calligra Sheets, with optional start position, wildcard examples and Calligra-specific errors.

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: =SEARCH("margin";"Profit Margin") returns the 1-based starting position 8 in the official KDE example. SEARCH looks for text without distinguishing letter case, and supports ? (one-character) and * (variable-length) wildcard patterns. Use FIND when the match must be case-sensitive. SEARCH returns a position, not the matching text.

Scope and validation: This guide covers Calligra 26.08.2, released October 8, 2026, based on the official KDE function documentation and pinned implementation accessed 2026-10-11. Example results are official documentation statements or independently reasoned expected values. No example has been executed in Calligra Sheets, and no genuine screenshots are supplied.

Exact syntax, argument order and default

=SEARCH(find_text;within_text;start_num)
Position Argument Required? KDE documented type Default Purpose
1 find_text Yes String None The text or wildcard pattern you need to find.
2 within_text Yes String None The containing text or cell reference.
3 start_num No Integer 1 One-based position at which to begin looking.

Although the displayed XML help uses the three-argument syntax, the actual Calligra registration sets two to three arguments. The source implementation initializes start_num=1 when it is omitted. The official handbook explains the same default. The English KDE examples use semicolon separators; confirm localized separators/function names in the installed formula editor. The output is a 1-based character position, not a byte count.

Wildcard semantics: Calligra 26.08.2 converts the search string using Qt QRegularExpression::fromWildcard with Qt::CaseInsensitive and unanchored matching. The official help documents ? to match one character and * to match a sequence. These are wildcard patterns, not a promise of directly supported raw regular-expression syntax. This guide does not assert untested escape rules for literal ? or *.

Reproducible worksheet: find a category marker

  1. Enter Description in A1, First location of bay in B1, and Start at character 6 in C1.
  2. Enter North Bay Market in A2 and Bayfront in A3.
  3. Enter =SEARCH("bay";A2) in B2: expected result 7 (N=1, the space=6, B=7). Enter =SEARCH("bay";A3) in B3: expected result 1.
  4. Enter =SEARCH("bay";A2;6) in C2: expected result 7. start_num changes where searching begins, not the numbering of the returned position.
Formula Expected result Evidence/interpretation
=SEARCH("margin";"Profit Margin") 8 Official KDE example.
=SEARCH("e";"Statements";6) 7 Official KDE example.
=SEARCH("bay";"North Bay Market") 7 Independent ASCII indexing.
=SEARCH("B?y";"North Bay") 7 The ? matches a between B and y; source wildcard rules.
=SEARCH("B*y";"North Bay") 7 * can match intervening characters; independent wildcard reasoning.
=SEARCH("BAN";"banana") 1 Case insensitive; independent reasoning.
=SEARCH("a";"banana";3) 4 Begin at index 3; next a at index 4.
=SEARCH("xyz";"banana") Error #N/A from source func_search returns Value::errorNA() when no match.
=SEARCH("a";"banana";0) Error #VALUE! from source start_num<=0 returns Value::errorVALUE().
=SEARCH("a";"banana";7) Error #VALUE! from source Start exceeds input length (6).

No error display text was observed in the application: #N/A and #VALUE! name the error kinds returned by Calligra source. The exact installed presentation may vary.

Advanced: extract a value after a delimiter

Assume cell A5 contains text ID:AB12. To extract the four-character code, find the colon and feed its position into MID:

=MID(A5;SEARCH(":";A5)+1;4)

SEARCH(":";"ID:AB12") has an expected 1-based position of 3, so MID(...;4;4) has the expected result AB12. This combination is independently reasoned using the documented semantics. There is no native Calligra test. If no colon exists, the SEARCH error propagates unless deliberately handled; consider checking the data or using an approved error-handling pattern.

To handle upper/lower-case supplier labels, =SEARCH("ABC";"ref-abc-2026") expects 5, while FIND("ABC";"ref-abc-2026") would not match the differently capitalized substring using FIND’s documented case sensitivity. Test imported Unicode case-folding in your installed language environment.

Important Calligra vs Excel error difference

The pinned implementation returns Value::errorNA() when a pattern cannot be found, but Value::errorVALUE() when start_num is nonpositive or exceeds the containing text’s length. This is a source-verified distinction. Microsoft documents Excel SEARCH returning #VALUE! when the desired text cannot be found, whereas the cited Calligra 26.08.2 source returns an N/A-kind error for the same no-match condition. Do not copy formulas that explicitly test an error code between products without adapting and testing them. LibreOffice’s SEARCH overview also documents a VALUE error when no text is found.

SEARCHB shares the SEARCH implementation

Calligra 26.08.2 registers the alternate name SEARCHB against the same implementation function as SEARCH (source lines 157–160). The documentation may describe byte-oriented variants historically, but registration alone does not justify claiming independent byte-counting semantics here. Do not assume Excel’s SEARCHB byte behavior. No installed Unicode/DBCS runtime test has been performed.

Troubleshooting, errors and limits

  • No match: The pinned source returns the N/A error kind, not a position of zero. Check spelling, punctuation and actual cell content.
  • Starting point too small/large: start_num must be greater than zero and not exceed the string length in this version’s implementation, otherwise it returns the VALUE error kind.
  • Case difference: SEARCH ignores case. Switch to FIND when case should matter.
  • Unexpected wildcard matches: Asterisks and question marks are patterns, not literal ordinary characters in this function. Inspect incoming lookup text if wildcard matching is not intended.
  • Character versus byte: Returned position counts Qt string characters as implemented, not a reliable byte offset; Unicode surrogate-pair behavior has not been native tested.
  • Empty pattern, Unicode edge cases or enormous strings: The documentation and checked source do not establish a universal user-visible result for every edge case. Avoid asserting one without native execution.
  • Formula syntax rejected: Confirm local function name and separator conventions (; in English KDE documentation), and whether your installed version matches the pinned release.

Excel and LibreOffice compatibility

All three applications provide ways to search case-insensitively, but error types and optional pattern modes are not interchangeable. Excel’s SEARCH documents wildcards and a VALUE error on an unsuccessful lookup; the pinned Calligra source returns N/A when no match. LibreOffice’s published SEARCH overview also specifies a VALUE error when no match is found. Calligra’s SEARCHB alternate name shares SEARCH’s source implementation rather than proving Excel’s byte-indexing behavior. For portability, first use simple literal strings and check the not-found/error path separately.

KDE SEARCH, FIND, MID, LEN, and IFERROR are official documentation links.

Official references and verification (accessed 2026-10-11)

Verification status: Official documentation checked and function source pinned; expected ASCII example outputs independently reasoned, with source-derived error kinds explicitly differentiated. Not executed in Calligra Sheets. No genuine Calligra screenshots are included.