Spreadsheet Practice

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

Formula (Excel and Google Sheets)

=IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2)

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

Result on the example
one result per row in B2, filled down to B7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

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.

=IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2)

Result"Desk lamp"

  1. A2"Desk lamp - Black"

    The value in A2.

  2. SEARCH(" - ",A2)10

    SEARCH: Position of text, not case-sensitive, allows wildcards.

    • find_text" - "
    • within_textA2"Desk lamp - Black"
  3. SEARCH(" - ",A2)-19

    The - operator subtracts.

  4. LEFT(A2,SEARCH(" - ",A2)-1)"Desk lamp"

    LEFT: Characters from the start of text.

    • textA2"Desk lamp - Black"
    • num_charsSEARCH(" - ",A2)-19
  5. IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2)"Desk lamp"

    IFERROR: Returns a fallback when the value is any error.

    • valueLEFT(A2,SEARCH(" - ",A2)-1)"Desk lamp"
    • value_if_errorA2"Desk lamp - Black"not used

In Google Sheets

The same formula works in Google Sheets: =IFERROR(LEFT(A2,SEARCH(" - ",A2)-1),A2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IF(ISNUMBER(SEARCH(" - ",A2)),LEFT(A2,SEARCH(" - ",A2)-1),A2)Desk lamp, Office chair, Monitor arm…Tests for the separator first, so other errors aren't hidden.
=TEXTBEFORE(A2," - ",,,,A2)Excel onlyDesk lamp, Office chair, Monitor arm…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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Fix #VALUE! from SEARCH or FIND when the text isn't found

In B2, return the text before " - ", or the whole name when there is no " - ". Fill down to B7.

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)).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.