Excel FIND and SEARCH functions with formula examples

This tutorial details the syntax and advanced applications of Excel's FIND and SEARCH functions. Previous articles covered the basic Find and Replace dialog; this expands on using Excel to automatically locate and extract data based on specified criteria.

- Excel's FIND Function

• Excel's SEARCH Function
• Key Differences: FIND vs. SEARCH
• Practical Formula Examples:
  • Extracting Strings Before/After a Specific Character
  • Locating the Nth Occurrence of a Character
  • Extracting N Characters Following a Character
  • Isolating Text Within Parentheses

Excel's FIND Function

The FIND function identifies the position of a character or substring within a text string.

Syntax:

FIND(find_text, within_text, [start_num])

• find_text: The character or substring to locate.
• within_text: The text string to search within (often a cell reference).
• start_num (optional): Specifies the starting character for the search (defaults to 1).

If find_text isn't found, FIND returns a #VALUE! error.

For example, =FIND("d", "find") returns 4. =FIND("a", "find") returns an error.

Key Considerations for FIND:

1. Case-sensitive: FIND is case-sensitive. Use SEARCH for case-insensitive searches.
2. No wildcards: FIND doesn't support wildcard characters.
3. First occurrence only: FIND returns the position of the first occurrence of find_text.
4. Empty string: If find_text is an empty string(""), FIND returns the position of the first character in within_text.
5. Error handling: #VALUE! is returned if find_text is not found, start_num exceeds within_text length, or start_num is 0 or negative.

Excel's SEARCH Function

SEARCH is similar to FIND, returning the position of a substring.

Syntax:

SEARCH(find_text, within_text, [start_num])

Unlike FIND, SEARCH is case-insensitive and supports wildcard characters (* and ?).

Examples:

=SEARCH("market", "supermarket") returns 6.

=SEARCH("e", "Excel") returns 1 (case-insensitive).

SEARCH also returns #VALUE! under the same error conditions as FIND.

FIND vs. SEARCH

• Case sensitivity: FIND is case-sensitive; SEARCH is not.
• Wildcards: SEARCH supports wildcards (* and ?); FIND does not.

Practical Formula Examples

FIND and SEARCH are rarely used alone; they're often combined with other functions (MID, LEFT, RIGHT).

Example 1: Extracting Strings Before/After a Character

Given a column of names (column A), extract first and last names into separate columns.

• First Name: =LEFT(A2, FIND(" ", A2)-1) or =LEFT(A2, SEARCH(" ", A2)-1)
• Last Name: =RIGHT(A2,LEN(A2)-FIND(" ",A2)) or =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

Example 2: Finding the Nth Occurrence of a Character

Find the position of the 2nd dash in a string (column A):

=FIND("-", A2, FIND("-",A2) 1)

For the 3rd occurrence:

=FIND("-",A2, FIND("-", A2, FIND("-",A2) 1) 2)

A simpler alternative using SUBSTITUTE and CHAR:

=FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3))

Example 3: Extracting N Characters After a Character

Extract the first 3 characters after the first dash:

=MID(A2, FIND("-",A2) 1, 3) or =MID(A2, SEARCH("-",A2) 1, 3)

To handle variable lengths after the dash:

=MID(A2, FIND("-",A2) 1, FIND("-", A2, FIND("-",A2) 1) - FIND("-",A2)-1)

Example 4: Extracting Text Within Parentheses

Extract text enclosed in parentheses (column A):

=MID(A2,SEARCH("(",A2) 1, SEARCH(")",A2)-SEARCH("(",A2)-1)

This tutorial provides a comprehensive guide to using FIND and SEARCH functions in Excel for advanced text manipulation. The next tutorial will cover the REPLACE function.

Download practice workbook

FIND and SEARCH formula examples

The above is the detailed content of Excel FIND and SEARCH functions with formula examples. For more information, please follow other related articles on the PHP Chinese website!