#VALUE! in FIND/SEARCH: When the string is not found

The #VALUE! error appears in Excel’s FIND and SEARCH functions whenever the text string specified in the find_text argument does not exist inside the target cell. Unlike standard programming lookup routines that return 0 or -1 when a match is absent, Excel terminates the formula evaluation and returns #VALUE!. When nested inside extraction functions like MID or LEFT, this error propagates and breaks the entire calculation.

Quick Fix

Wrap the search expression in ISNUMBER() to return a clean TRUE or FALSE without errors: =ISNUMBER(SEARCH("target", A1)). For text extractions where the search character might be missing, wrap the position lookup in IFERROR(): =LEFT(A1, IFERROR(SEARCH("-", A1) - 1, LEN(A1))).

---

FIND vs. SEARCH: Key Operational Differences

While both functions locate the position of a substring within text, their operational rules determine why and when they fail:

Feature	FIND Function	SEARCH Function
Case Sensitivity	Yes ("ABC" will not match "abc")	No ("ABC" matches "abc")
Wildcards (*, ?)	Not supported (treated as literal characters)	Supported
Missing Match Result	#VALUE!	#VALUE!
Syntax	=FIND(find_text, within_text, [start_num])	=SEARCH(find_text, within_text, [start_num])

If you use FIND("dept", A1) and the cell contains "Dept-A", FIND will throw #VALUE! because of the uppercase "D". Switching to SEARCH("dept", A1) resolves casing mismatches immediately.

---

Common Failure Scenarios and Solutions

Scenario 1: Using FIND/SEARCH as a Condition in an IF Statement

Spreadsheet authors often try to write conditional logic like this:

=IF(SEARCH("Admin", A2), "Yes", "No")   <-- Incorrect

If "Admin" is not in cell A2, SEARCH does not return FALSE. It generates #VALUE!, which halts the IF statement and displays the error.

The Fix:
Wrap the search function in ISNUMBER(). When SEARCH succeeds, it returns a numeric character position (e.g., 4), which ISNUMBER() evaluates to TRUE. When SEARCH fails and outputs #VALUE!, ISNUMBER() cleanly converts it to FALSE.

=IF(ISNUMBER(SEARCH("Admin", A2)), "Yes", "No")

For broader guidance on building robust conditional statements, see Using IF(ISNUMBER(SEARCH… to avoid #VALUE! errors.

---

Scenario 2: Dynamic Text Truncation with LEFT or MID

A frequent requirement is extracting everything to the left of a delimiter (such as a space, hyphen, or comma):

=LEFT(A2, FIND(" ", A2) - 1)

If a row contains a single word (e.g., "Acme" without a space), FIND(" ", A2) cannot locate the delimiter. It returns #VALUE!, which is passed to LEFT, breaking the formula for that entire row as detailed in #VALUE! in LEFT/RIGHT/MID: Non-numeric length arguments.

Fix Option A: Append the Delimiter (The Concatenation Trick)

Append the search character directly to the searched text argument inside FIND:

=LEFT(A2, FIND(" ", A2 & " ") - 1)

How it works: If A2 contains "Acme", A2 & " " becomes "Acme ". FIND locates the appended space at position 5, and LEFT extracts 5 - 1 = 4 characters ("Acme"), preventing the #VALUE! error without requiring conditional functions.

Fix Option B: Use IFERROR Fallback

=LEFT(A2, IFERROR(FIND(" ", A2) - 1, LEN(A2)))

If no space exists, IFERROR catches #VALUE! and tells LEFT to return the full length of the string (LEN(A2)).

Fix Option C: Modern Dynamic Array Text Functions (Excel 365 / 2021+)

If using modern Excel versions, replace LEFT and FIND combinations with TEXTBEFORE:

=TEXTBEFORE(A2, " ", , , , A2)

The optional sixth argument tells TEXTBEFORE what to return if the delimiter is not found (in this case, the original text A2).

---

Scenario 3: Start Number Exceeds String Length

Both functions include an optional third argument: start_num.

=FIND("-", A2, 10)

If start_num is set to a value less than or equal to zero, or if start_num is greater than the total character count of the text (LEN(A2)), Excel returns #VALUE! regardless of whether the character exists elsewhere in the cell.

• Check: Ensure start_num is a positive integer between 1 and =LEN(A2).
• Fix: Wrap the start index calculation in a safety bounds check: =IF(LEN(A2) >= 10, FIND("-", A2, 10), "String too short")

---

Diagnostic Checklist for Lingering Errors

If you believe the string is present in the target cell but FIND or SEARCH still returns #VALUE!:

1. Check for Hidden Spaces: If looking for " - " (space-hyphen-space), verify whether the cell uses a single space, double space, or tab character.
2. Examine Wildcards in FIND: If searching for "item*" using FIND, it will fail unless the cell literally contains an asterisk character. Use SEARCH when wildcards are required.
3. Check for Serial Data Types: If you are searching within a cell that holds an actual date or number formatted as text, FIND searches the underlying serial value, not the display format. For instance, searching for "/2024" in a true date cell will fail because the underlying value is a serial number like 45300. Use =SEARCH("2024", TEXT(A2, "yyyy-mm-dd")) instead.

For managing and suppressing expected search failures globally across large data sets, see IFERROR vs. IFNA: When to be surgical with error catching.