Excel FIND and SEARCH Functions
last modified October 5, 2026
The FIND and SEARCH functions are text functions that
locate one string inside another. They return the position of the first
character of the found text, counting from 1. The two functions look alike but
differ in two important ways: FIND is case-sensitive and does not
accept wildcards, while SEARCH ignores case and supports the
? and * wildcards. This tutorial covers their syntax,
the differences between them, and practical techniques such as extracting
substrings and finding the second occurrence.
FIND and SEARCH Function Basics
Both functions search for find_text inside within_text
and return the 1-based position of the first character of the match. If the
text is not found, or if start_num is out of range, the result is
#VALUE!.
| Function | Description | Syntax |
|---|---|---|
| FIND | Case-sensitive search without wildcards | =FIND(find_text, within_text, [start_num]) |
| SEARCH | Case-insensitive search with wildcards | =SEARCH(find_text, within_text, [start_num]) |
The arguments are the same for both functions. The third argument is optional and lets you skip part of the text before searching.
| Argument | Meaning |
|---|---|
| find_text | The text to look for. In SEARCH it may contain the
wildcards ? and *. |
| within_text | The text to search in. It can be a literal string, a cell reference, or another text expression. |
| start_num | Optional. The position at which to start searching. It defaults to 1.
The returned position is always counted from the beginning of
within_text, even when characters are skipped. |
Because the position is counted from the first character of
within_text, a match found after start_num still
reports its absolute position. If find_text is an empty string,
the function returns start_num.
FIND vs SEARCH
The following table summarizes the behavioral differences. Choose
FIND when case matters, and SEARCH when it does not or
when you need pattern matching.
| Feature | FIND | SEARCH |
|---|---|---|
| Case sensitivity | Case-sensitive | Case-insensitive |
| Wildcards | Not supported | ?, *, and ~ supported |
| Empty find_text | Returns start_num | Returns start_num |
| Text not found | #VALUE! | #VALUE! |
| Typical use | Exact, case-aware matching | Flexible, case-insensitive matching |
=FIND("a", "ABC") ' #VALUE!, no lowercase "a"
=SEARCH("a", "ABC") ' 1, case is ignored
The first formula fails because FIND cannot see a lowercase
a in uppercase ABC. The second succeeds because
SEARCH treats the two cases as equal.
Basic FIND Example
This example demonstrates the simplest use of FIND to locate a
substring within text.
=FIND("e", "Excel")
This formula returns 4 because the lowercase e is the fourth
character of Excel. FIND is case-sensitive, so the
uppercase E at position 1 is not a match. Searching for
"N" returns #VALUE! because there is no
N in the word.
Basic SEARCH Example
This example shows SEARCH locating a substring while ignoring case
differences.
| A | B |
|---|---|
| Excel Functions | |
| =SEARCH("fun", A1) |
Cell A1 contains Excel Functions. The formula in B1 looks for the
lowercase text fun even though the cell stores Fun.
=SEARCH("fun", A1)
The formula returns 7 because Fun starts at the seventh character,
counting the space at position 6. SEARCH would find
FUN, Fun, or fun in the same text.
Case Sensitivity
When case matters, FIND is the right choice. This example compares
the two functions on the names MacDonald and
macdonald.
| A | B |
|---|---|
| MacDonald | =FIND("Mac", A1) |
| macdonald | =FIND("Mac", A2) |
Cell B1 returns 1 because Mac appears exactly at the start of
MacDonald. Cell B2 returns #VALUE! because the text
is lowercase. Replacing FIND with SEARCH would return
1 for both rows.
=FIND("Mac", A1) ' 1 for "MacDonald", #VALUE! for "macdonald"
=SEARCH("Mac", A2) ' 1, case is ignored
Use FIND when the exact casing carries meaning, such as product
codes, passwords, or proper nouns.
Wildcards with SEARCH
SEARCH accepts three wildcard characters in
find_text. FIND treats all of them as literal text.
| Wildcard | Meaning | Example |
|---|---|---|
? |
Matches any single character | =SEARCH("b?t", "bat") returns 1 |
* |
Matches any sequence of characters, including none | =SEARCH("b*t", "boot") returns 1 |
~ |
Escapes the next ?, *, or ~ so
it is treated literally |
=SEARCH("~?", "a?b") returns 2 |
The next example matches a version number whose first digit can vary.
| A | B |
|---|---|
| Excel 2023 | |
| =SEARCH("Excel ?", A1) |
=SEARCH("Excel ?", A1)
The formula returns 1 because the pattern matches at the very start of the
text. The literal space in the pattern matches the space after
Excel, and the ? matches the single digit
2. To search for a literal question mark, prefix it with a tilde,
as in =SEARCH("~?", A1).
Using start_num
The optional start_num argument tells the function where to begin
searching. It is useful for skipping a known prefix or for finding later
occurrences.
| A | B |
|---|---|
| banana | |
| =FIND("a", A1, 5) |
=FIND("a", A1, 5)
banana has an a at positions 2, 4, and 6. Searching
from position 5 skips the first two and returns 6. The result is always counted
from the start of the text, not from start_num. If
start_num is less than 1 or greater than the length of the text,
the result is #VALUE!.
Extracting Substrings with MID
Because FIND and SEARCH return a position,
MID is their natural companion. Adding 1 to the position skips the
found character itself.
| A | B |
|---|---|
| name@example.com | |
| =MID(A1, FIND("@", A1)+1, 100) |
=MID(A1, FIND("@", A1)+1, 100)
FIND returns 5, the position of the @, so
MID starts at 6. The large length of 100 simply captures all
remaining text, giving example.com. The same idea extracts text
between two delimiters.
=MID(A1, FIND("(", A1)+1, FIND(")", A1)-FIND("(", A1)-1)
For (example) the formula returns example. The length
is the distance between the closing and opening parentheses, minus one, so the
parentheses themselves are excluded.
Finding the Second Occurrence
Nesting a search inside its own start_num argument finds the next
match. This example locates the second space in a name.
=FIND(" ", A1, FIND(" ", A1)+1)
If A1 contains John Michael Smith, the inner FIND
returns 5 for the first space, and the outer search starts at 6 and returns 13
for the second space. Nest the pattern once more to reach the third occurrence.
Handling Errors
When the text is not found, both functions return #VALUE!.
Wrapping them in IFERROR produces a friendlier result.
=IFERROR(FIND("z", "Excel"), "Not found")
There is no z in Excel, so the formula returns
Not found instead of an error. To test only whether a substring
exists, compare the result with a number, as in
=ISNUMBER(SEARCH("fun", A1)).
Finding Multiple Possible Values
SEARCH accepts an array constant, which lets one formula test for
several substrings at once. This example checks for common Office file
extensions.
=IF(SUM(--ISNUMBER(SEARCH({".doc",".xls",".ppt"}, A1)))>0, "Office", "Other")
SEARCH returns one result per extension, ISNUMBER
turns each result into TRUE or FALSE, and the double
unary minus converts those to 1 and 0. If the sum is greater than zero, A1
contains at least one Office extension and the formula returns
Office.
FIND and SEARCH Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| find_text does not occur in within_text | #VALUE! error | Wrap the call in IFERROR or test with ISNUMBER first |
| start_num is less than 1 or greater than the text length | #VALUE! error | Keep start_num between 1 and LEN(within_text) |
| Case mismatch in FIND | #VALUE! for text that is visibly present | Use SEARCH for case-insensitive matching |
| Wildcards used in FIND | The pattern is treated as literal text | Use SEARCH when you need ? or * |
| Literal ? or * in SEARCH | Unexpected match or position | Escape the character with a tilde, for example ~? |
| Result expected to start at start_num | Position looks too small or too large | Remember the result is always relative to the start of within_text |
| Empty find_text | Returns start_num, which may look wrong | An empty string matches at the start position by design |
The FIND and SEARCH functions are the foundation of
text parsing in Excel. FIND gives exact, case-sensitive matching,
while SEARCH adds case-insensitive matching and the
? and * wildcards. Together with MID,
LEFT, and RIGHT, they turn a single position into a
complete substring-extraction toolkit. Once you are comfortable with
start_num and nested searches, you can locate any occurrence and
split text exactly where you need it.
Author
List all Excel Formulas.