MS Excel: FIND function to locate the position of a substring

The FIND function in Excel is used to locate the position of a substring (a piece of text) within another string. It returns the position number of the first character where the substring is found. The function is case-sensitive and does not allow wildcards, making it ideal for precise string matching.

Common use cases include:

• Extracting specific parts of a string

• Finding the location of symbols (like “@” in emails)

• Identifying patterns within data

• Preparing data for further text processing (like LEFT, RIGHT, MID)

Syntax

=FIND(find_text, within_text, [start_num])

Parameters:

Argument	Description
find_text	The substring you want to find
within_text	The text string where you want to search
start_num	(Optional) The character position to start searching from (default is 1)

Returns: A number representing the position of the first character of find_text within within_text.

Examples with Tables

Example 1: Find a Word in a Sentence

=FIND("Excel", "I love Excel formulas")

Formula	Result
=FIND("Excel", "I love Excel formulas")	8

Explanation: The word “Excel” begins at the 8th character.

Example 2: Case Sensitivity

=FIND("excel", "I love Excel formulas")

Formula	Result
=FIND("excel", "I love Excel formulas")	#VALUE!

Note: FIND is case-sensitive, so “excel” (lowercase) doesn't match “Excel” (uppercase E).

Example 3: Using start_num Argument

=FIND("o", "Good morning", 5)

Formula	Result
=FIND("o", "Good morning", 5)	8

It starts searching from position 5 and finds the next “o” at position 8.

Example 4: Dynamic Use with MID

Suppose A1 contains:John.Doe@example.com

You want to extract the first name:

=LEFT(A1, FIND(".", A1) - 1)

A1	Formula	Output
John.Doe@example.com	=LEFT(A1, FIND(".", A1) - 1)	John

Errors and How to Handle

Situation	Error	Solution
Substring not found	#VALUE!	Use with IFERROR to avoid breaking formulas
Case mismatch	#VALUE!	Use SEARCH if case-insensitivity is okay
start_num less than 1	#VALUE!	Ensure it’s 1 or greater

FIND vs SEARCH

Feature	FIND	SEARCH
Case-sensitive	✅ Yes	❌ No
Supports wildcards	❌ No	✅ Yes (*, ?)
Return type	Position (Number)	Position (Number)

Summary

Feature	Detail
Function Name	FIND
Purpose	Locate position of one string in another
Case Sensitivity	✅ Yes
Returns	Number or #VALUE! error
Common Pairing	With LEFT, MID, RIGHT, IFERROR

Related Functions

Function	Purpose
SEARCH	Like FIND, but case-insensitive
LEFT	Extract text from the beginning of a string
RIGHT	Extract text from the end of a string
MID	Extract text from the middle of a string
TEXTSPLIT (365)	Newer way to split text using delimiters

Final Thoughts

The FIND function is a powerful tool for locating specific text patterns, especially when case sensitivity matters. It's frequently used in data parsing, text analysis, and cleaning operations. Pair it with IFERROR and extraction functions to create dynamic and safe formulas.