How to Use SEARCH in Excel
The SEARCH function locates text within another text string and returns the position where it first appears. Unlike its cousin FIND, SEARCH is case-insensitive, which makes it ideal for real-world...
Key Insights
- SEARCH is case-insensitive and supports wildcards, making it more flexible than FIND for pattern matching and text analysis in Excel
- Combining SEARCH with ISNUMBER, MID, and IF functions unlocks powerful text parsing capabilities for extracting domains, validating formats, and automating data categorization
- Always wrap SEARCH in IFERROR to handle cases where text isn’t found, preventing #VALUE! errors from breaking your formulas
Introduction to the SEARCH Function
The SEARCH function locates text within another text string and returns the position where it first appears. Unlike its cousin FIND, SEARCH is case-insensitive, which makes it ideal for real-world data where capitalization varies unpredictably.
SEARCH returns a number representing the character position of the found text. If you’re searching for “@” in “john@company.com”, SEARCH returns 5 because the @ symbol is the fifth character. This numeric output becomes incredibly useful when combined with other text functions to extract, validate, or transform data.
The real power of SEARCH emerges in practical scenarios: validating that email addresses contain “@”, checking if customer feedback mentions specific keywords, parsing log files for error codes, or building conditional formatting rules that highlight cells containing certain terms. Any time you need to ask “does this cell contain X?” or “where does Y appear in this text?”, SEARCH is your tool.
Basic SEARCH Syntax and Parameters
The SEARCH function follows this structure:
SEARCH(find_text, within_text, [start_num])
find_text: The text you’re looking for. This can be a literal string in quotes ("@"), a cell reference (A2), or even a wildcard pattern.
within_text: The text you’re searching within. Typically a cell reference containing the data you want to analyze.
start_num (optional): The character position where you want to start searching. Defaults to 1 if omitted. Useful when you need to find the second or third occurrence of text.
Here’s a basic example to find the @ symbol in email addresses:
=SEARCH("@", A2)
If cell A2 contains “sarah@example.com”, this formula returns 6. If A2 contains “invalid-email”, the formula returns #VALUE! because no @ symbol exists.
To find the position of a domain extension:
=SEARCH(".com", A2)
For “sarah@example.com”, this returns 14, indicating where “.com” begins.
The start_num parameter lets you search past the first occurrence:
=SEARCH("o", "Boston Commons", 1) // Returns 2 (first 'o')
=SEARCH("o", "Boston Commons", 3) // Returns 4 (second 'o')
SEARCH vs. FIND: Key Differences
SEARCH and FIND appear similar but behave differently in crucial ways. Understanding these differences prevents hours of debugging frustration.
Case Sensitivity: SEARCH ignores case; FIND respects it. SEARCH treats “Apple”, “APPLE”, and “apple” identically. FIND treats them as distinct strings.
Wildcard Support: SEARCH supports asterisk (*) and question mark (?) wildcards. FIND does not.
Here’s a side-by-side comparison:
// Cell A1 contains "Apple Pie"
=SEARCH("apple", A1) // Returns 1 (found, case-insensitive)
=FIND("apple", A1) // Returns #VALUE! (not found, case-sensitive)
=SEARCH("APPLE", A1) // Returns 1 (found)
=FIND("APPLE", A1) // Returns #VALUE! (not found)
=FIND("Apple", A1) // Returns 1 (exact case match)
When to use SEARCH: User-generated content, data imports where case varies, pattern matching with wildcards, or any scenario where you want flexible matching.
When to use FIND: Parsing structured data where case matters (like programming code), distinguishing between acronyms and words (NASA vs. nasa), or when you need strict matching.
For 90% of business data analysis, SEARCH is the better choice because real-world data rarely maintains consistent capitalization.
Using Wildcards with SEARCH
Wildcards transform SEARCH from a simple text finder into a pattern matching engine. Excel supports two wildcards:
Asterisk (*): Matches any number of characters (including zero) Question mark (?): Matches exactly one character
Suppose you have product codes following the pattern “PRD-XXX-YYYY” where X represents category digits and Y represents variable-length identifiers:
// Cell A1 contains "PRD-847-ALPHA2023"
=SEARCH("PRD-*", A1) // Returns 1 (matches the pattern)
=SEARCH("PRD-???-*", A1) // Returns 1 (three digits after PRD-)
=SEARCH("PRD-8??-*", A1) // Returns 1 (starts with 8)
=SEARCH("PRD-9??-*", A1) // Returns #VALUE! (doesn't start with 9)
Practical wildcard applications:
// Find cells containing any email from a specific domain
=SEARCH("*@company.com", A2)
// Locate invoice numbers with a specific format (INV-####)
=SEARCH("INV-????", A2)
// Find product codes where the third character is "7"
=SEARCH("??7*", A2)
To search for literal asterisk or question mark characters, precede them with a tilde (~):
=SEARCH("~*", A2) // Finds actual asterisk character
=SEARCH("~?", A2) // Finds actual question mark character
Combining SEARCH with Other Functions
SEARCH becomes exponentially more powerful when combined with other Excel functions. Here are the most valuable combinations:
ISNUMBER + SEARCH: Creates TRUE/FALSE conditions for filtering or conditional logic.
// Check if cell contains "@" (validates email format)
=ISNUMBER(SEARCH("@", A2))
// Check if customer feedback mentions "refund"
=ISNUMBER(SEARCH("refund", B2))
// Use in IF statement
=IF(ISNUMBER(SEARCH("urgent", A2)), "High Priority", "Normal")
MID + SEARCH: Extracts text portions based on dynamic positions.
// Extract domain from email (everything after @)
=MID(A2, SEARCH("@", A2) + 1, LEN(A2))
// For "john@example.com", this returns "example.com"
Breaking down this formula: SEARCH finds the @ position, we add 1 to start after it, and LEN(A2) ensures we capture all remaining characters.
LEFT/RIGHT + SEARCH: Extracts text before or after a delimiter.
// Extract username from email (everything before @)
=LEFT(A2, SEARCH("@", A2) - 1)
// Extract file extension (everything after last period)
// Note: This assumes only one period
=RIGHT(A2, LEN(A2) - SEARCH(".", A2))
Nested SEARCH: Find second or third occurrences by combining searches.
// Find second occurrence of "/" in a URL path
=SEARCH("/", A2, SEARCH("/", A2) + 1)
Handling Errors and Edge Cases
SEARCH returns #VALUE! when the search text isn’t found. This error propagates through formulas and breaks calculations. Always handle this scenario explicitly.
IFERROR wrapper: The cleanest solution for most cases.
=IFERROR(SEARCH("@", A2), "Not Found")
=IFERROR(SEARCH("@", A2), 0) // Return 0 instead of text
ISERROR + IF: More verbose but offers finer control.
=IF(ISERROR(SEARCH("@", A2)), "Invalid Email", "Valid Email")
Handling blank cells: SEARCH treats empty cells as empty strings (""), which may cause unexpected behavior.
// Robust email validation that handles blanks
=IF(A2="", "Empty", IF(ISNUMBER(SEARCH("@", A2)), "Valid", "Invalid"))
Case sensitivity reminder: Remember SEARCH is case-insensitive. If you need case-sensitive matching, use FIND instead.
// This finds "test", "TEST", "Test", "TeSt", etc.
=SEARCH("test", A2)
Real-World Applications
Customer Feedback Categorization: Automatically tag support tickets based on keywords.
// Cell B2 categorizes feedback in A2
=IF(ISNUMBER(SEARCH("refund", A2)), "Billing Issue",
IF(ISNUMBER(SEARCH("bug", A2)), "Technical Issue",
IF(ISNUMBER(SEARCH("feature", A2)), "Feature Request",
"General Inquiry")))
Email Domain Extraction for Analysis: Build a pivot table of customer email domains.
// Extract domain from email in A2
=IFERROR(MID(A2, SEARCH("@", A2) + 1, SEARCH(".", A2, SEARCH("@", A2)) - SEARCH("@", A2) - 1), "Invalid")
This formula finds the @ symbol, starts extraction one character after it, and stops at the period following the @.
Log File Parsing: Identify error entries in system logs.
// Flag rows containing error codes
=IF(ISNUMBER(SEARCH("ERR-*", A2)), "Error Found", "")
// Count error severity
=IF(ISNUMBER(SEARCH("ERR-5??", A2)), "Critical",
IF(ISNUMBER(SEARCH("ERR-4??", A2)), "Warning", "Info"))
Data Validation Dashboard: Create a summary showing data quality issues.
// Check if phone numbers contain only digits and dashes
=IF(ISNUMBER(SEARCH("*[a-z]*", A2)), "Contains Letters", "Valid Format")
// Validate URL format
=IF(AND(ISNUMBER(SEARCH("http", A2)), ISNUMBER(SEARCH("://", A2))), "Valid URL", "Invalid URL")
Dynamic Conditional Formatting: Highlight cells based on content without manual rules.
Create a helper column with this formula, then format based on the helper column:
=ISNUMBER(SEARCH("overdue", A2)) + ISNUMBER(SEARCH("urgent", A2)) + ISNUMBER(SEARCH("critical", A2))
This returns 0 if none of the keywords appear, 1 if one appears, 2 if two appear, etc. Format cells where this value is greater than 0.
The SEARCH function excels at transforming unstructured text data into structured, analyzable information. Master it alongside ISNUMBER, MID, and IF, and you’ll handle text manipulation tasks that would otherwise require manual review or complex VBA scripts. The key is always wrapping SEARCH in error handlers and thinking about edge cases before your formulas break in production.