Many text formulas depend on knowing exactly where a character or word appears inside a string. The FIND and SEARCH functions solve this — both return the character position of one text string within another. For example, FIND("@", "sarah@company.com") returns 6 because the "@" sits at position 6. You can then feed that number into LEFT, RIGHT, or MID to extract exactly what you need. Additionally, these functions power conditional checks, data validation, and dynamic extraction across every Excel version.
What Is the Syntax for FIND and SEARCH?
Both functions share the same three arguments. The only differences are in how they treat case and wildcards.
| Argument | Required? | What it does |
|---|---|---|
| find_text | Required | The character or substring you want to locate. Can be typed directly in quotes or referenced from a cell. |
| within_text | Required | The full string to search inside. Usually a cell reference, but can also be a text value in quotes. |
| start_num | Optional | The character position to begin searching from. Defaults to 1 (the first character). Use this to skip a known prefix or to find a second occurrence. |
How Do FIND and SEARCH Differ?
At first glance, the two functions look identical. However, two key differences determine which one to choose for a given task.
| Feature | FIND | SEARCH |
|---|---|---|
| Case sensitivity | Case-sensitive. "apple" and "Apple" are different. | Case-insensitive. "apple" and "APPLE" match equally. |
| Wildcard support | No wildcards. Characters are matched literally. | Supports ? (any single character) and * (any sequence). |
| Best used for | Exact, case-sensitive matching — e.g. locating "ID:" not "id:" | General text search where case varies — e.g. finding "@" in emails |
| Error when not found | #VALUE! | #VALUE! |
| Available in | All versions from Excel 2000 | All versions from Excel 2000 |
Example 1: Find a Character's Position in a String
Both functions return an integer representing the position of the first matching character. Understanding this output is essential before combining either function with LEFT, RIGHT, or MID. The visual below shows how positions count from left to right, starting at 1.
Example 2: Dynamic Text Extraction with LEFT and RIGHT
The real power of FIND and SEARCH comes when you nest them inside LEFT or RIGHT. Instead of hardcoding a character count, you let the function measure the delimiter position dynamically. As a result, the formula adapts to strings of any length.
Example 3: Use start_num to Find the Second Occurrence
By default, FIND and SEARCH locate the first occurrence of a character. However, you can start the search from a position past the first match by using the start_num argument. This technique lets you locate the second, third, or any subsequent occurrence of a delimiter.
How Does start_num Work?
You first find the position of the first occurrence. Then you pass that position plus 1 as the start_num of a second FIND. Excel consequently skips everything up to that point and searches the rest of the string. The result is still measured from character 1 of the original string.
FIND("-", A2, prev_pos + 1). Alternatively, SUBSTITUTE can replace the Nth occurrence with a unique marker, then FIND locates that marker in one step.
Example 4: Check Whether a String Contains a Substring
FIND and SEARCH return #VALUE! when the search text is missing. You can turn this error-or-number behaviour into a TRUE/FALSE check by wrapping the result in ISNUMBER. This pattern is widely used for conditional labelling, filtering flags, and data validation.
=COUNTIF(A2,"*urgent*") — is simpler for single-cell checks, but ISNUMBER(SEARCH(...)) is more flexible inside complex formulas.
Example 5: Use Wildcards with SEARCH
SEARCH supports two wildcard characters that FIND does not. A question mark (?) matches exactly one character of any kind. An asterisk (*) matches any sequence of characters, including zero characters. These wildcards make SEARCH especially useful when you know the pattern of a substring but not its exact value.
Wildcard Examples with SEARCH
For instance, searching for "V?" finds "V1", "V2", "VA", or any two-character sequence beginning with V. Similarly, searching for "ver*ion" finds "version", "verification", or any word that starts with "ver" and ends with "ion". This flexibility is particularly useful for matching product version codes and reference numbers.
Example 6: Handle Missing Delimiters with IFERROR
Both functions throw #VALUE! when the search text does not exist in the string. This breaks formulas across an entire column if even one row has missing or inconsistent data. Therefore, always wrap FIND and SEARCH in IFERROR when working with real-world datasets where the delimiter may be absent.
How to Fix Common FIND and SEARCH Errors
#VALUE! error
Either the search text does not exist in the string, or start_num is invalid (less than 1 or greater than the string length). To diagnose the issue, test the formula in an empty cell with a known string. If the delimiter is sometimes missing, wrap the formula in IFERROR as shown in Example 6.
Wrong position returned — off by one
Check whether extra spaces appear before or after the text. FIND and SEARCH count spaces as characters, so a leading space shifts every position by 1. Wrap within_text in TRIM to clean the string first: =FIND("-", TRIM(A2)). This removes leading, trailing, and duplicate internal spaces.
FIND returns a position but the wrong one
By default, both functions return the first occurrence. If your string has multiple delimiters and you need the second or third, use the start_num argument as shown in Example 3. Alternatively, SUBSTITUTE combined with FIND offers a compact way to locate any Nth occurrence.
Frequently Asked Questions
-
What is the difference between FIND and SEARCH in Excel?FIND is case-sensitive and does not support wildcards. SEARCH is case-insensitive and supports the ? and * wildcard characters. For most everyday text extraction — splitting emails, splitting names, locating delimiters — SEARCH is the better choice because it handles inconsistent capitalisation without errors. Use FIND specifically when the exact case of the character matters.
-
What does FIND return if the text is not found?Both FIND and SEARCH return a #VALUE! error when the search text does not exist in the string. To handle this gracefully, wrap the formula in IFERROR: =IFERROR(FIND("@",A2), 0) returns 0 when "@" is missing instead of showing an error. Alternatively, use ISNUMBER(FIND(...)) to get a TRUE/FALSE result — ISNUMBER automatically converts errors to FALSE.
-
How do I find the second occurrence of a character?Use the start_num argument to skip past the first match. First find the position of the first occurrence, then add 1 and pass that as start_num to a second FIND: =FIND("-", A2, FIND("-", A2) + 1). This finds the second hyphen in A2. Repeat the nesting to find the third, fourth, and so on.
More Questions About FIND and SEARCH
-
Does SEARCH support wildcards?Yes, SEARCH supports two wildcard characters. A question mark (?) matches exactly one character of any kind. An asterisk (*) matches any sequence of characters, including zero. FIND, by contrast, does not support wildcards at all. To search for a literal question mark or asterisk with SEARCH, prefix the character with a tilde (~): use "~?" to find a real question mark.
-
How do I check if a cell contains a specific word?Wrap SEARCH inside ISNUMBER: =ISNUMBER(SEARCH("word",A2)). This returns TRUE if "word" appears anywhere in A2 (case-insensitive), or FALSE if it does not. No IFERROR is needed because ISNUMBER converts errors to FALSE automatically. For a case-sensitive check, replace SEARCH with FIND.
-
Which Excel versions support FIND and SEARCH?Both functions are available in every Excel version from Excel 2000 onwards, including Microsoft 365, Excel 2024, 2021, 2019, 2016, 2013, 2010, 2007, and earlier. They also work in Excel for the web and Google Sheets. No special version, add-in, or setting is needed.