How to Use the FIND Function in Excel to Locate Text Strings
=FIND(find_text, within_text, [start_num])The FIND function is a powerful text manipulation tool in Excel that enables users to locate the position of specific text within a larger string. This function returns the starting position of the found text as a numeric value, making it essential for data analysis, text parsing, and string manipulation tasks. Whether you're working with customer data, product codes, or complex text entries, FIND helps you pinpoint exact character locations within your data. Understanding FIND is crucial for intermediate Excel users who need to extract, validate, or manipulate text data efficiently. The function is case-sensitive, meaning it distinguishes between uppercase and lowercase letters, which is important when working with codes or identifiers that require precise matching. By mastering FIND, you'll unlock the ability to build more sophisticated formulas that combine it with functions like MID, RIGHT, and LEFT to extract meaningful information from unstructured text data. The FIND function works seamlessly across all modern Excel versions from 2007 through Excel 365, ensuring consistency in your spreadsheets whether you're using legacy systems or cloud-based solutions. Its straightforward syntax and reliable performance make it a foundational skill for anyone working with text-heavy datasets in Excel.
Syntax & Parameters
The FIND function syntax is structured as =FIND(find_text, within_text, [start_num]), where each parameter plays a specific role in locating text. The first parameter, find_text, is required and represents the exact text string you want to locate. This parameter is case-sensitive, so searching for 'Excel' will not find 'excel' in lowercase. The second required parameter, within_text, specifies the text string where you want to search. This is typically a cell reference or a text string enclosed in quotation marks. The third parameter, start_num, is optional and defaults to 1, meaning the search begins at the first character. When you specify a start_num value greater than 1, FIND ignores all characters before that position and begins searching from the specified position onward. This feature is particularly useful when you need to find multiple occurrences of the same text within a string by adjusting the starting position with each search. Practical implementation requires understanding that FIND returns the position as a number. If the search text is not found, FIND returns a #VALUE! error. The function counts all characters, including spaces, punctuation, and special characters. For instance, if searching for 'World' in 'Hello World', FIND returns 7 because 'W' is the 7th character including the space. When combining FIND with other functions like MID or RIGHT, you can extract text segments based on the position FIND identifies, creating powerful text parsing solutions.
find_textwithin_textstart_numPractical Examples
Finding Product Code Position in SKU String
=FIND("-",A1)This formula searches for the first hyphen in cell A1 containing 'NIKE-45782-M'. FIND returns 5, indicating the hyphen is at position 5. You can then use =MID(A1,FIND("-",A1)+1,5) to extract '45782'.
Locating Email Domain in Customer Email Address
=FIND("@",B2)For an email like '[email protected]' in cell B2, this formula returns 11, the position of the '@' symbol. This enables you to extract the domain using =RIGHT(B2,LEN(B2)-FIND("@",B2)) or validate email format.
Finding Specific Word Position in Product Description
=FIND("Limited",C3)If cell C3 contains 'Premium Limited Edition Leather Jacket', FIND returns 9, showing 'Limited' starts at the 9th character position. This can be used in an IF statement to apply conditional formatting or pricing rules for limited items.
Key Takeaways
- FIND returns the numeric position of text within a string and is case-sensitive, making it ideal for precise text location tasks
- The function requires two parameters (find_text and within_text) and accepts an optional start_num parameter to begin searching from a specific position
- Combine FIND with MID, RIGHT, LEFT, and LEN functions to extract, validate, or manipulate text based on located positions
- Use IFERROR to handle situations where search text doesn't exist, preventing #VALUE! errors in your spreadsheets
- FIND works consistently across Excel 2007-365 and is available in Google Sheets and LibreOffice, ensuring broad compatibility
Pro Tips
Combine FIND with IFERROR to create robust formulas that handle missing text gracefully without displaying error values.
Impact : Prevents #VALUE! errors from disrupting your spreadsheet, making reports more professional and user-friendly. Allows formulas to continue calculating even when search text isn't found.
Use the start_num parameter to find multiple occurrences by nesting FIND functions: =FIND('text',A1,FIND('text',A1)+1) locates the second occurrence.
Impact : Enables advanced text parsing for data with repeated patterns. Allows extraction of specific instances when multiple matches exist in a single cell.
Remember FIND is case-sensitive; use SEARCH for case-insensitive searches when working with user-entered data that may have inconsistent capitalization.
Impact : Prevents missed matches due to case variations. Improves data processing reliability when dealing with inconsistently formatted user input.
Combine FIND with LEN to calculate distances between text elements: =FIND('end',A1)-FIND('start',A1) gives the character distance between two markers.
Impact : Enables sophisticated text analysis and validation. Useful for measuring text segments, validating data format consistency, and extracting variable-length content.
Useful Combinations
Extract Text Between Two Delimiters
=MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1)This combination finds two hyphens and extracts the text between them. FIND locates the first hyphen, then finds the second hyphen starting after the first. MID extracts the text between these positions. Perfect for parsing formatted data like 'FIRST-MIDDLE-LAST'.
Extract Text After Specific Character
=RIGHT(A1,LEN(A1)-FIND("@",A1))This formula finds the '@' symbol position and uses RIGHT to extract everything after it. Useful for extracting email domains from complete email addresses. For '[email protected]', it returns 'example.com' (including the @).
Conditional Formatting Based on Text Location
=IF(ISERROR(FIND("Urgent",A1)),"Normal","Priority")Combines FIND with IF and ISERROR to check if specific text exists. If 'Urgent' is found, returns 'Priority'; otherwise returns 'Normal'. Useful for categorizing tasks, tickets, or items based on content keywords.
Common Errors
Cause: The search text (find_text) does not exist in the string being searched (within_text). For example, =FIND('xyz',A1) when A1 contains 'Hello World'.
Solution: Verify the text you're searching for exists in the target cell. Use IFERROR to handle cases where text might not be found: =IFERROR(FIND('text',A1),'Not Found'). Double-check for spelling, spacing, and case sensitivity differences.
Cause: The cell reference in the formula is invalid or the referenced cell has been deleted. This typically occurs when using FIND with a cell reference that no longer exists.
Solution: Verify that all cell references in your formula are correct and the referenced cells exist. If you've deleted columns or rows, update the formula references accordingly. Use absolute references ($A$1) if you're copying the formula to prevent reference shifts.
Cause: Excel doesn't recognize 'FIND' as a valid function name. This occurs when the function is misspelled or Excel's language settings use a different function name.
Solution: Ensure correct spelling of FIND. In some non-English Excel versions, the function might be named differently (e.g., TROVAR in Spanish). Check your Excel language settings and use the appropriate localized function name if necessary.
Troubleshooting Checklist
- 1.Verify the search text (find_text) exists in the target string and check for spelling accuracy and extra spaces
- 2.Confirm case sensitivity matches your requirement; use SEARCH instead if case-insensitive search is needed
- 3.Check that cell references are valid and cells haven't been deleted; update references if spreadsheet structure changed
- 4.Ensure the start_num parameter doesn't exceed the length of the text string being searched
- 5.Test the formula with a simple example first (like =FIND('a','apple')) before applying to complex datasets
- 6.Wrap FIND in IFERROR to handle #VALUE! errors when text might not always be present
Edge Cases
Searching for an empty string or zero-length text
Behavior: FIND returns 1, as the empty string technically exists at position 1 of any string. This can produce unexpected results in complex formulas.
Solution: Add validation to check that find_text is not empty before using FIND: =IF(LEN(find_text)=0,'Invalid',FIND(find_text,within_text))
This edge case is rare but important when building dynamic formulas where search text comes from user input or other cells.
Searching in very long strings (>32,767 characters)
Behavior: Excel cells have a character limit of 32,767 characters. FIND will work within this limit but cannot search beyond it.
Solution: Break long strings into smaller segments or use alternative text processing methods. Consider using Power Query for handling extremely large text datasets.
This limitation is rarely encountered in typical business spreadsheets but becomes relevant with concatenated data or imported large text blocks.
Using FIND with special regex characters like *, ?, or ~
Behavior: FIND treats these as literal characters and doesn't interpret them as wildcards. It searches for the actual asterisk, question mark, or tilde symbol.
Solution: Use SEARCH if you need wildcard matching, or REGEX in Excel 365 for pattern-based matching. For literal special character searches, FIND is actually the correct choice.
This distinguishes FIND from SEARCH, which does support wildcards. Understanding this difference prevents confusion when searching for special characters.
Limitations
- •FIND is case-sensitive and cannot search for text regardless of capitalization. Use SEARCH function for case-insensitive searches when working with inconsistently formatted data.
- •FIND only returns the position of the first occurrence of text. Finding multiple occurrences requires nesting multiple FIND functions or using more complex formulas, which can become unwieldy.
- •FIND cannot use wildcard characters or regular expressions for pattern matching. Use SEARCH for wildcards or REGEX in Excel 365 for advanced pattern-based searches.
- •FIND returns a #VALUE! error when search text is not found, requiring error handling with IFERROR or other error-management functions to prevent spreadsheet disruption.
Alternatives
Case-insensitive search and supports wildcard characters (* and ?) for pattern matching. More flexible for general text location tasks where case variation exists.
When: Use SEARCH when you need to find text regardless of capitalization, such as locating 'excel' in 'EXCEL', 'Excel', or 'excel'. Ideal for user-generated content where case consistency isn't guaranteed.
Provides advanced pattern matching and text extraction capabilities. Enables complex text parsing with regular expressions in Excel 365.
When: Use FILTERXML for sophisticated text parsing scenarios like extracting all digits from mixed alphanumeric strings or validating complex patterns that FIND cannot handle.
Modern function for pattern-based text matching and extraction. Supports regular expressions for powerful text manipulation without nesting multiple functions.
When: Use REGEX in Excel 365 when you need pattern matching beyond simple text location, such as finding email addresses, phone numbers, or specific text patterns within cells.
Compatibility
✓ Excel
Since 2007
=FIND(find_text, within_text, [start_num])✓Google Sheets
=FIND(search_for, text_to_search, [optional_starting_at])Syntax is identical with parameter names slightly different. Google Sheets FIND is also case-sensitive and returns #VALUE! when text not found.
✓LibreOffice
=FIND(find_text, within_text, [start_num])