How to Use FIND in Excel
The FIND function is one of Excel's most powerful text manipulation tools, yet it often gets overlooked in favor of flashier features. At its core, FIND does one thing exceptionally well: it tells...
Key Insights
- FIND is case-sensitive and returns the numeric position of text within a string, making it essential for precise text parsing in Excel formulas
- Combining FIND with MID, LEFT, and RIGHT enables powerful text extraction patterns like pulling domains from emails or splitting names into components
- Always wrap FIND in IFERROR to handle cases where the search text doesn’t exist, preventing #VALUE! errors from breaking your spreadsheet
Introduction to the FIND Function
The FIND function is one of Excel’s most powerful text manipulation tools, yet it often gets overlooked in favor of flashier features. At its core, FIND does one thing exceptionally well: it tells you exactly where a piece of text appears within another string. It returns a number representing the character position, starting from 1.
Why does this matter? Because knowing the position of specific characters lets you extract, manipulate, and parse text data with surgical precision. Need to pull everything after the “@” symbol in an email address? FIND locates the symbol, then you use other functions to grab what you need. Splitting full names into first and last names? FIND locates the space, giving you the split point.
The most critical characteristic of FIND is its case-sensitivity. If you search for “A” in “apple”, FIND returns an error because it’s looking for uppercase A specifically. This precision is exactly what you want when parsing structured data where case matters.
Here’s the simplest possible example:
=FIND("@", "user@email.com")
This returns 5, because the “@” symbol is the fifth character in the string. That number becomes the foundation for more complex text manipulation.
FIND Syntax and Parameters
FIND takes three parameters, though you’ll only use all three in specific scenarios:
=FIND(find_text, within_text, [start_num])
find_text: The text you’re searching for. This can be a literal string in quotes, a cell reference, or even a single character. Remember, it’s case-sensitive.
within_text: The text you’re searching within. Typically this is a cell reference containing your data, but it can also be a text string or the result of another function.
start_num: Optional. This tells FIND where to begin searching. The default is 1 (start at the beginning). This parameter is crucial when you need to find the second, third, or nth occurrence of text.
The start_num parameter is where things get interesting. Say you have “banana” and want to find the second “a”. You can’t just use =FIND("a", "banana") because that always returns 2 (the position of the first “a”). Instead:
=FIND("a", "banana", 3)
This starts searching from position 3, skipping the first “a” and returning 4 (the position of the second “a”). This technique chains together when finding multiple occurrences, which we’ll cover later.
One critical behavior: FIND returns a #VALUE! error if it doesn’t find the search text. This isn’t a bug—it’s by design. You need to handle this explicitly, which is why IFERROR becomes your best friend when using FIND.
FIND vs. SEARCH: Key Differences
Excel provides two functions that seem to do the same thing: FIND and SEARCH. They’re not interchangeable, and choosing the wrong one will cause problems.
FIND characteristics:
- Case-sensitive
- No wildcard support
- Returns exact position or error
SEARCH characteristics:
- Case-insensitive
- Supports wildcards (* and ?)
- Returns position or error
Here’s where this matters in practice:
=FIND("A", "apple") // Returns #VALUE! error
=SEARCH("A", "apple") // Returns 1
The FIND function can’t locate uppercase “A” in “apple” because no uppercase A exists. SEARCH doesn’t care about case, so it finds the “a” immediately.
When should you use each? Use FIND when case matters—when you’re parsing data with specific capitalization patterns, like file extensions (.PDF vs .pdf), product codes, or programming syntax. Use SEARCH when you’re looking for text regardless of how it’s capitalized, like searching for keywords in descriptions or finding names in text.
The wildcard difference matters too. If you need to find “bt” to match “bat”, “bit”, or “but”, you must use SEARCH. FIND treats asterisks as literal characters, so it only matches the exact string “bt”.
My recommendation: default to FIND for structured data parsing. Its case-sensitivity prevents false matches and makes your formulas more predictable. Use SEARCH only when you specifically need case-insensitivity or wildcards.
Practical Applications with Other Functions
FIND’s real power emerges when you combine it with other text functions. Here are the patterns I use constantly:
Extracting email domains:
=MID(A1, FIND("@", A1)+1, LEN(A1))
This finds the “@” symbol, starts extracting one character after it, and continues to the end of the string. If A1 contains “john@company.com”, this returns “company.com”.
Extracting first names from full names:
=LEFT(A1, FIND(" ", A1)-1)
This locates the first space, then extracts everything to the left of it, minus one character (to exclude the space itself). “John Smith” becomes “John”.
Extracting last names:
=RIGHT(A1, LEN(A1)-FIND(" ", A1))
This finds the space, calculates how many characters come after it, then extracts that many characters from the right. “John Smith” becomes “Smith”.
Extracting file extensions:
=MID(A1, FIND(".", A1)+1, LEN(A1))
For “document.pdf”, this returns “pdf”. Note that this simple version breaks with multiple periods (like “archive.tar.gz”), which requires more sophisticated techniques.
Extracting text between two delimiters:
=MID(A1, FIND("[", A1)+1, FIND("]", A1)-FIND("[", A1)-1)
This extracts everything between square brackets. For “Product [XYZ] Description”, it returns “XYZ”. The formula finds the opening bracket, starts one character after, then calculates the length by finding the distance between brackets.
These patterns work because FIND provides the anchor points—the specific positions where your delimiters exist. MID, LEFT, and RIGHT then extract based on those positions.
Error Handling with IFERROR
Every FIND formula should include error handling unless you’re absolutely certain the search text exists. When FIND doesn’t locate the text, it returns #VALUE!, which cascades through dependent formulas and breaks your spreadsheet.
The solution is IFERROR:
=IFERROR(FIND("@", A1), "No @ symbol found")
If the “@” symbol exists, FIND returns its position and IFERROR passes that through. If it doesn’t exist, IFERROR catches the error and returns your specified message instead.
For more complex formulas, wrap the entire expression:
=IFERROR(MID(A1, FIND("@", A1)+1, LEN(A1)), "Invalid email")
This protects against emails without “@” symbols, returning “Invalid email” instead of an error.
You can also return blank cells for cleaner spreadsheets:
=IFERROR(FIND("@", A1), "")
A blank return value is often better than error messages when you’re processing large datasets where missing data is expected.
One advanced technique: use IFERROR to provide default values that enable further processing. Instead of returning an error message, return a number that makes sense for your use case:
=IFERROR(FIND(" ", A1), LEN(A1)+1)
This returns the position of the first space, or if no space exists, returns a position just past the end of the string. This prevents errors in extraction formulas that depend on FIND’s result.
Advanced Techniques: Finding Nth Occurrence
Finding the first occurrence of text is straightforward. Finding the second, third, or nth occurrence requires a technique that most Excel users don’t know.
Finding the second occurrence:
=FIND(" ", A1, FIND(" ", A1)+1)
This nests FIND calls. The inner FIND locates the first space. The outer FIND starts searching one position after that first space, effectively finding the second space.
For “John Michael Smith”, the inner FIND returns 5 (position of first space). The outer FIND starts at position 6 and returns 13 (position of second space).
Finding the third occurrence:
=FIND(" ", A1, FIND(" ", A1, FIND(" ", A1)+1)+1)
This extends the pattern, nesting another level deeper. It’s readable if you work from the inside out, but it gets unwieldy quickly.
Alternative approach using SUBSTITUTE:
=FIND(CHAR(1), SUBSTITUTE(A1, " ", CHAR(1), 2))
This technique temporarily replaces the nth occurrence of your search text with a rare character (CHAR(1) is a non-printing character), then finds that character. The “2” parameter in SUBSTITUTE means “replace the second occurrence only.”
This approach is more elegant for finding specific occurrences beyond the second or third:
=FIND(CHAR(1), SUBSTITUTE(A1, ",", CHAR(1), 5))
This finds the fifth comma in a string, which would be painful with nested FIND calls.
Practical application—extracting middle names:
=IFERROR(MID(A1, FIND(" ", A1)+1, FIND(" ", A1, FIND(" ", A1)+1)-FIND(" ", A1)-1), "")
This extracts text between the first and second spaces. For “John Michael Smith”, it returns “Michael”. The IFERROR handles cases where no middle name exists.
These techniques transform FIND from a simple lookup function into a powerful parsing engine. Combined with proper error handling and other text functions, you can parse virtually any structured text data Excel throws at you.