SEARCH in WPS Spreadsheet: Find Text Without Case Sensitivity

• Introduced: documented by May 16, 2019 in the official WPS lesson; original introduction is not established.
• Availability: WPS Office for Windows Personal; numeric release not identified in the lesson. The examples follow that historical product documentation.
• Scope: a current supported build range and equivalent behavior in other WPS clients remain unverified.
• Checked: October 14, 2026. Function-specific official documentation and independent example calculations; no native WPS execution was performed.

=SEARCH(find_text,within_text,[start_num])

find_text is the text or wildcard pattern to find. within_text is the source to search. The optional start_num is one-based and defaults to 1 in the official Windows Personal lesson. Omit the square brackets when entering an actual formula.

The lesson documents case-insensitive matching, ? for one character, * for a sequence, and ~ before a question mark or asterisk to search for that literal symbol. It also documents #VALUE! for no match or a start position outside the permitted string positions.

Examples use English names and commas. Adjust separators for the installed locale without changing quoted punctuation. No regular-expression syntax is assumed.

=SEARCH("sHEET","Google Sheets")
=SEARCH("-","INV-482")

Expected results: 8 and 4. The first formula ignores the different capitalization. In the second, the hyphen follows the three-letter prefix.

A position is a number, not the matched text. If the task is to extract a field, use the position as an argument to a text function. Keep a simple position formula in a helper cell while developing a longer expression.

For AA-BB-CC in A2:

=SEARCH("-",A2)
=SEARCH("-",A2,4)

Expected results: 3 and 6. Beginning the second search at position 4 skips the first hyphen, but the reported answer remains position 6 in the original string. It is not position 3 relative to the shortened search area.

A reusable second-occurrence formula is:

=SEARCH("-",A2,SEARCH("-",A2)+1)

The inner SEARCH finds position 3; adding one begins the outer search at position 4. This assumes a second hyphen exists. For AA-BBCC, the second search has no match and the documented result is #VALUE!.

=SEARCH("b?x","Box-17")
=SEARCH("N*th","North Depot")
=SEARCH("~*","A*B")
=SEARCH("~?","Q?7")

Expected positions: 1, 1, 2, 2. The question mark in b?x matches the single letter o. The asterisk in N*th allows the intervening letters or. The escaped patterns locate actual punctuation.

SEARCH is a substring search, so a wildcard pattern is not automatically a whole-cell validation rule. A match within a longer label can still be valid for SEARCH while failing a business rule that requires the entire label to match.

Enter INV-0042-CA in D2. In E2 and F2 enter:

=SEARCH("-",D2)
=SEARCH("-",D2,E2+1)

Expected positions: 4 and 9. Then use MID:

=MID(D2,E2+1,F2-E2-1)

Expected result: 0042. Extraction starts immediately after the first separator, and the second separator is excluded from the count. The result remains text, preserving the leading zeroes.

For JOB-718-US, the same helper-based pattern finds positions 4 and 8 and extracts 718. The field length changes without requiring a hard-coded count.

The example assumes two hyphens delimit the required field. Extra separators inside the field change that interpretation. Validate the input structure before applying the parser to unfamiliar records.

These documented failure controls should produce #VALUE!:

=SEARCH("Z","ABC")
=SEARCH("A","ABC",0)
=SEARCH("A","ABC",4)

When failure is expected and should be flagged, a visible fallback can use IFERROR:

=IFERROR(SEARCH("-",A2),"Review source")

For AA-BB, the expected result is 3; for AABB, it is Review source. IFERROR catches other formula errors too, so the message is deliberately broader than “separator missing.” Inspect the unsuppressed formula while debugging.

This guide uses character positions. SEARCHB follows the byte-oriented convention described in a separate WPS lesson, so its positions can differ for Chinese or full-width text. Do not feed a byte index into a character-position formula without checking the intended units.

Case-insensitive search can be unsuitable for identifiers whose case matters. Keep a case-sensitive comparison or separately verified search routine for those fields. The historical Windows lesson does not establish behavior in every WPS client or every Unicode boundary case.

• Official WPS SEARCH lesson, published May 16, 2019 (Chinese; Windows Personal).
• Editorial testing standards.

The dedicated lesson establishes the identity and historically documented behavior used here. Outputs are original examples checked independently, not screenshots or native test results. The lesson is not evidence of an exact current build range, a retirement date or support in every WPS client.