# How to fix #VALUE! from SEARCH or FIND when the text isn't found

Catch the "not found" case: =IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2) returns the whole text when there is no separator.

**Excel:** `=IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Product | Base name |
| Desk lamp - Black |  |
| Office chair |  |
| Monitor arm - Dual |  |
| Webcam |  |
| Headset - USB-C |  |
| Laptop stand - Silver |  |

Formula in B2, filled to B7: `=IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2)`

Result: Desk lamp; Office chair; Monitor arm; Webcam; Headset; Laptop stand

## How it works

SEARCH returns #VALUE! when it can't find the text, so for "Office chair", which has no " - ", LEFT gets an error and passes it on. IFERROR catches that case and returns the whole name instead.

## Other ways

- `=IF(ISNUMBER(SEARCH(" - ",A2)),LEFT(A2,SEARCH(" - ",A2)-1),A2)`: Tests for the separator first, so other errors aren't hidden.
- `=TEXTBEFORE(A2," - ",,,,A2)`: Excel 365: TEXTBEFORE's last argument is what to return when the separator isn't found.

## Common mistakes

- IFERROR hides any other problem too. The ISNUMBER version reacts only to the missing separator.
- FIND is case-sensitive and SEARCH isn't; both return #VALUE! when nothing is found.
- Search for " - " with its spaces, or "USB-C" is split at its hyphen.

## Questions

**How do I get the text after the separator instead?**

`=IFERROR(MID(A2,SEARCH(" - ",A2)+3,99),"")`.

**How do I just test whether the separator is there?**

`=ISNUMBER(SEARCH(" - ",A2))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/search-or-find-value-error