Learn about find formula in Excel

1. Introduction of Find  and Syntax
2. Baisc Example of Find
3. Start with Specific Position
4. Handling Errors formula

Introduction

The FIND function in Excel is used to locate the position of a specific character or substring within a text string. It returns the position (as a number) of the first occurrence of the substring, starting from a specified position.

Syntax

=FIND(find_text, within_text, [start_num])

Parameters

• Find_text : character or subsring you want to locate or find
• Within_text : The text string where the search will be performed.
• [Start_num] (optional) : The position in the text string to start the search. Default is 1 (beginning of the text).

Key Point

• Find function is case sensitive. Use search for case-insensitive search.
• If the find_text is not found, it returns a #VALUE! error.
• Start_num allows you to skip a portion of the text.

Example:

1. Basic Use in Excel Find Formula

Formula : =FIND(“l”, ”apple”)

Result wil be: 4

Case-sensitive

Formula: 
=Find(“I”, “APPLE”)

Result well be: error

Case-sensitive (with search formula)

Formula: =search(“I”, ”APPLE”)

Result will be : 4

2. Start with Specific Position in Excel Find Formula

FORMULA:

=FIND("a", "Elephant", 4)

The result will be : 6

3. Substring Search

Formula:

=FIND("cat", "Concatenate")

The Result will be : 4

4. Handling Errors : If the substring is not found then error (#VALUE!) will come. We use below formula for error handling

Formula:

If we use only find function the result will be like this:

If we use iferror function then result will be like this:

=IFERROR(FIND("z", "Banana"), "Not Found")

Example with MID for Extraction

To extract the domain name from an email address:

Formula:

=MID(A1, FIND("@", A1) + 1, LEN(A1) - FIND("@", A1))

If A1 = “john.doe@example.com”, this formula returns “example.com”.

Related topic

1. AND formula : The AND function is a premade function in Excel, which returns TRUE or FALSE based on two or more conditions.
2. Countifs Formula : The Excel COUNTIFS function works for give the count of cells that meet one or more criteria. COUNTIFS can be used to count cells that contain dates, numbers, and text, with logical operators (>,<,<>,=).
3. VBA Code for Find formula : The Find method is a powerful tool in Excel VBA that helps you locate a specific value within a range of cells.
4. Find formula: as per microsoft official site