Cleaning imported data
FIND vs SEARCH: choose case-sensitive or wildcard text matching
Both functions return the character position where a substring starts, or #VALUE! when absent—but FIND enforces exact uppercase/lowercase while SEARCH ignores case and interprets * and ? wildcards.
One label containing both lowercase pump and capitalized Pump
In A2, enter the 22-character maintenance note "line pump / Spare Pump". Lowercase "pump" begins at character 6, whereas capitalized "Pump" begins at character 19.
| Text in cell | Formula | Result |
|---|---|---|
| line pump / Spare Pump (A2) | =FIND("Pump", A2) | 19 |
| line pump / Spare Pump (A2) | =SEARCH("Pump", A2) | 6 |
| Grade A* hose (A3) | =SEARCH("~*", A3) | 8 |
Compare FIND and SEARCH on the same cell
FIND("Pump", A2) skips "pump" at position 6 because the P does not match case, returning 19. SEARCH("Pump", A2) ignores letter case and matches the first occurrence at position 6.
When a substring is missing altogether—such as FIND("Valve", A2)—both functions return #VALUE! (not #N/A). Wrap either function in ISNUMBER(...) when you want a clean TRUE or FALSE contains check.
=FIND("Pump",A2)Watch for wildcards inside SEARCH
SEARCH treats ? (any single character) and * (any sequence of characters) as wildcards. Searching for "*" in A3 ("Grade A* hose") matches right at position 1! Prefix literal asterisks or question marks with a tilde: =SEARCH("~*", A3) finds the actual asterisk at position 8 (or use FIND("*", A3), which never uses wildcards).
=SEARCH("~*",A3)Try the example
Use the original values or change them to test your understanding.