How to fix #N/A error in SEARCH Function in Excel?

The #N/A error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #REF! error in SEARCH Function in Excel?

The #REF! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #VALUE! error in SEARCH Function in Excel?

The #VALUE! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #DIV/0! error in SEARCH Function in Excel?

The #DIV/0! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #NAME? error in SEARCH Function in Excel?

The #NAME? error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

SEARCH Function in Excel

Master the SEARCH function to find text within strings. Learn case-insensitive searching, wildcards, practical examples, and error solutions.

Excel

Google Sheets

text

intermediate

Syntax Preview

Excel

Google Sheets

=SEARCH(find_text, within_text, [start_num])

Quick Answer

Comprehensive Explanation

Syntax and Parameters

How to Use SEARCH - Step by Step

## What is the SEARCH Function?

The SEARCH function is a fundamental text manipulation function in Excel and Google Sheets, designed to locate a substring within a larger text string and return its starting position. First introduced in early Excel versions, it has become essential for data analysts, administrators, and anyone working with text data who needs to perform case-insensitive text searches.

### Core Functionality

At its core, SEARCH operates using a sequential character matching methodology:

1. **Input Processing:** SEARCH converts all text to the same case internally for comparison
2. **Calculation Logic:** It scans character by character from the start position until finding a match
3. **Result Generation:** Returns the numeric position of the first matching character

This three-step process ensures case-insensitive matching, maintains performance even with long strings, and provides consistent numeric output. The function can handle strings up to 32,767 characters and processes searches efficiently on standard systems.

### When to Use SEARCH

SEARCH is optimal for:

**Case-Insensitive Searches:** When you need to find text regardless of uppercase or lowercase variations, because SEARCH treats 'Excel', 'EXCEL', and 'excel' as identical

**Wildcard Pattern Matching:** For flexible searches requiring pattern matching where you can use * to represent any number of characters or ? for a single character

**Data Validation:** In scenarios where you need to verify that certain text patterns exist within your data, such as checking for specific domains in email addresses

Avoid SEARCH when exact case matching is required (use FIND instead) or when working with binary data where case sensitivity matters.

### Key Advantages Over Alternatives

While functions like FIND and TEXTSEARCH serve similar purposes, SEARCH distinguishes itself through:

- **Case Insensitivity:** Unlike FIND, SEARCH ignores case differences automatically
- **Wildcard Support:** The function handles * and ? wildcards for flexible pattern matching
- **Universal Compatibility:** Works across all Excel and Google Sheets versions
- **Simpler Syntax:** More intuitive than regular expression functions for basic searches

## Step-by-Step Guide

### Step 1: Prepare Your Data
Organize your text data in a column or range. Ensure cells don't contain leading or trailing spaces that might affect search results (use TRIM if necessary). If searching for special characters like asterisks or question marks, you'll need to escape them with a tilde (~).

### Step 2: Enter the Basic Formula
Click on the cell where you want the result and type `=SEARCH(`. Enter your search text in quotes or reference a cell, then add a comma. Reference the cell containing the text to search, then close the parenthesis. For example: `=SEARCH("@", A2)` finds the @ symbol position in email addresses.

### Step 3: Handle Errors Gracefully
Since SEARCH returns #VALUE! when text isn't found, wrap it in IFERROR for cleaner results: `=IFERROR(SEARCH("keyword", A2), "Not found")`. This approach prevents error values from disrupting your spreadsheet calculations.

### Step 4: Use Results for Further Processing
Combine SEARCH with other functions for powerful text manipulation. Use with MID to extract text after a found position, with LEN and RIGHT to get text after a delimiter, or with SUBSTITUTE to replace found text. The numeric position returned by SEARCH serves as input for these text extraction functions.

Practical Examples

Common Errors and Solutions

#VALUE!

SEARCH cannot find the specified text

Cause:

The find_text doesn't exist within the within_text string, or start_num is greater than the length of within_text

Solution:

1. Verify the search text exists in your data 2. Check for extra spaces using TRIM 3. Ensure start_num is valid 4. Use IFERROR to handle missing text: =IFERROR(SEARCH(text, cell), "Not Found")

Prevention:

Always validate your data and use IFERROR wrapper for production formulas

Frequency: 45%

Example:

#NAME?

Excel doesn't recognize the function name

Cause:

Misspelled function name, missing quotes around text, or using SEARCH in a version that doesn't support it

Solution:

1. Check spelling - it's SEARCH not SEACH or SERACH 2. Ensure text strings are in quotes 3. Verify Excel version compatibility

Prevention:

Use Formula AutoComplete and always put literal text in quotes

Frequency: 20%

Example:

#REF!

Invalid cell reference in formula

Cause:

Referenced cells have been deleted or the formula refers to cells that don't exist

Solution:

1. Check all cell references are valid 2. Re-select the cells if needed 3. Use named ranges for stability

Prevention:

Use structured references or named ranges instead of direct cell references

Frequency: 10%

Example:

Wrong Position Returned

SEARCH returns unexpected position number

Cause:

Not accounting for SEARCH being case-insensitive, or forgetting that position counting starts at 1, not 0

Solution:

1. Remember SEARCH is case-insensitive (use FIND for case-sensitive) 2. Position 1 means first character 3. Check if there are multiple occurrences

Prevention:

Test with simple examples first and verify position counting

Frequency: 15%

Example:

Best Practices and Advanced Tips

Use ISNUMBER(SEARCH(...)) to get TRUE/FALSE instead of position/error. Perfect for IF statements and conditional formatting: =IF(ISNUMBER(SEARCH("urgent", A1)), "High Priority", "Normal").

To find the second or third occurrence, use nested SEARCH with start_num: =SEARCH("a", A1, SEARCH("a", A1) + 1) finds the second 'a'. Build a recursive pattern for multiple finds.

To search for literal * or ? characters, escape them with tilde (~). Search for '?' using '~?', for '*' using '~*', and for '~' itself using '~~'.

SEARCH can slow down with thousands of rows. For better performance: limit search range, use helper columns for complex searches, and consider array formulas for bulk operations.

SEARCH: case-insensitive, supports wildcards, returns #VALUE! if not found. FIND: case-sensitive, no wildcards, also returns #VALUE!. Choose SEARCH for flexible user input, FIND for exact matching.

SEARCH vs Alternative Functions

Platform Compatibility

## Comparison with Alternatives

### SEARCH vs FIND

| Feature | SEARCH | FIND | Best Choice |
|---------|--------|------|-------------|
| Case Sensitivity | Insensitive | Sensitive | SEARCH for user input |
| Wildcards | Supports * and ? | No wildcards | SEARCH for patterns |
| Performance | Slightly slower | Slightly faster | FIND for exact match |
| Error Handling | #VALUE! if not found | #VALUE! if not found | Both need IFERROR |
| Excel Versions | All versions | All versions | Both universal |

### When to Use Each Function

**Use SEARCH when:**
- Users might enter text in different cases
- You need pattern matching with wildcards
- Working with inconsistent data formats
- Building user-friendly search features

**Use FIND when:**
- Case matters (passwords, codes)
- You need exact character matching
- Performance is critical with large data
- Working with case-sensitive identifiers

**Use TEXTSEARCH (Sheets) or XLOOKUP (Excel 365) when:**
- You need regular expressions
- Searching arrays or ranges
- Need more advanced pattern matching
- Want to return values, not positions