Mastering the FIND Function in Excel
Learn how to use the FIND function in Excel, compare it with SEARCH, and build robust text extraction with IFERROR, MID, and LEN, plus practical examples.
The FIND function in Excel locates a substring within a text string and returns the starting position. It is case-sensitive and supports an optional start_num to begin the search at a later position. Syntax: FIND(find_text, within_text, [start_num]).
Understanding the function find excel
The FIND function locates a substring within a text string and returns its starting position. It is one of Excel's core text functions and is essential when you need to parse or extract data from semi-structured text, such as IDs, codes, or product names. The function is case-sensitive, meaning 'A' and 'a' are treated as different characters, and a not-found result triggers an error that you can catch with IFERROR. The syntax is FIND(find_text, within_text, [start_num]), where find_text is the substring to locate, within_text is where you search, and start_num optionally skips characters at the start.
=FIND("apple","pineapple")Output: 5
=FIND("A","pineapple")Output: #VALUE!
=FIND("p","pineapple",2)Output: 6
In practice, you combine FIND with other functions (LEFT, RIGHT, MID) to extract parts of text once you know the position.
Steps
Estimated time: 30-45 minutes
- 1
Open workbook and identify target column
Open the Excel file that contains the text data you want to parse. Locate the column where the strings reside and outline exactly what you want to extract (the position, or a substring derived from the position).
Tip: Draft a small test dataset to verify the logic before applying to the full sheet. - 2
Write a basic FIND formula
In a helper cell, enter a simple formula like =FIND("apple",A2) to confirm the position of the substring. Validate the numeric output for several rows to ensure the function behaves as expected.
Tip: Start with a short example to minimize confusion. - 3
Add error handling
Wrap FIND with IFERROR to prevent #VALUE! from propagating when the substring is missing.
Tip: Choose a consistent, neutral placeholder such as 0 or an empty string. - 4
Extract text with MID/LEFT/RIGHT
Use the returned position to pull surrounding text. Combine with MID to capture a substring, or LEFT/RIGHT for segments before/after the found text.
Tip: Test with a fixed length first, then adapt length as needed. - 5
Find the nth occurrence
If you need the nth match, replace the nth delimiter with a marker using SUBSTITUTE, then FIND that marker to obtain the position.
Tip: Begin with a simple delimiter like '-' and build up complexity gradually. - 6
Validate and document
Cross-check outputs on multiple rows and document assumptions. Consider using LET (Excel 365) to improve readability and performance.
Tip: Comment formulas or maintain a small data dictionary for future users.
Prerequisites
Required
- Required
- Basic knowledge of Excel formulasRequired
Optional
- Familiarity with IFERROROptional
- Access to a sample datasetOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Copy formulaCopy a formula from the formula bar | Ctrl+C |
| Paste formulaPaste into a target cell | Ctrl+V |
| Open FindOpen Find dialog across workbook | Ctrl+F |
| Edit active cellEdit the content of the selected cell | F2 |
People Also Ask
What does FIND return if the substring is not found?
FIND returns a #VALUE! error when the substring cannot be located. Wrap the function with IFERROR to provide a friendly fallback.
If the substring isn’t found, FIND returns an error; you can provide a fallback with IFERROR.
Is the FIND function case-sensitive?
Yes. FIND distinguishes uppercase from lowercase. If you need a case-insensitive search, use the SEARCH function.
Yes, FIND is case-sensitive. For case-insensitive, use SEARCH.
What is the difference between FIND and SEARCH?
FIND is case-sensitive; SEARCH is not. Both return the starting position of the match or #VALUE! if not found.
FIND is case-sensitive, while SEARCH ignores case and returns the position or an error.
How can I find the nth occurrence of a character?
Use SUBSTITUTE to replace the nth occurrence with a marker, then FIND that marker to get its position.
You can locate the nth occurrence by replacing the nth delimiter with a marker and finding it.
Can FIND be used on numbers or only text?
FIND works on text. If your data is numeric, first convert it to text with TEXT or TEXTJOIN-based methods.
FIND works on text data; convert numbers to text if needed.
What are good practices for large datasets?
Use LET to simplify formulas, wrap FIND with IFERROR, and consider indexing common substrings to reduce repeated work.
For large datasets, use LET, wrap FIND with IFERROR, and cache results where possible.
The Essentials
- Find returns a starting position, not text
- FIND is case-sensitive; use SEARCH for case-insensitive tasks
- Combine FIND with MID/LEFT/RIGHT for extraction
- Wrap FIND with IFERROR for missing substrings
- Use LET to simplify complex pipelines