The FIND formula with example in Excel

The FIND function in Excel is used to locate the position of a substring within a text string. It is case-sensitive and does not support wildcard characters.

Syntax

excel
FIND(find_text, within_text, [start_num])

• find_text: The substring you want to find.
• within_text: The text string within which you want to search for the substring.
• start_num: Optional. The position within within_text to start the search. The default is 1.

Example: Basic Usage

Suppose you have the text "Hello World" in cell A1 and you want to find the position of the substring "World".

1. In cell A1, enter the text:

   excel
   Hello World
   

2. In cell B1, enter the formula to find the position of "World":

   excel
   =FIND("World", A1)
   

   This will output 7, because "World" starts at the 7th character of "Hello World".

Example: Case Sensitivity

Suppose you have the text "Hello World" in cell A2 and you want to find the position of the substring "world" (note the lowercase 'w').

1. In cell A2, enter the text:

   excel
   Hello World
   

2. In cell B2, enter the formula to find the position of "world":

   excel
   =FIND("world", A2)
   

   This will output a #VALUE! error, because FIND is case-sensitive and "world" does not match "World".

Example: Using the Start Number

Suppose you have the text "abcabcabc" in cell A3 and you want to find the position of the second "abc".

1. In cell A3, enter the text:

   excel
   abcabcabc
   

2. In cell B3, enter the formula to find the second "abc":

   excel
   =FIND("abc", A3, 4)
   

   This will output 4, because the second occurrence of "abc" starts at the 4th character.

Practical Use Cases

1. Extracting Substrings: Using FIND in combination with other text functions like LEFT, RIGHT, and MID to extract specific parts of a text string.
2. Data Validation: Ensuring that certain substrings exist within text strings.
3. Conditional Formatting: Highlighting cells that contain specific substrings.

Example: Extracting a Domain from an Email Address

Suppose you have an email address "user@example.com" in cell A4 and you want to extract the domain name.

1. In cell A4, enter the email address:

   excel
   user@example.com
   

2. In cell B4, enter the formula to find the position of the "@" symbol:

   excel
   =FIND("@", A4)
   

3. In cell C4, enter the formula to extract the domain name using MID:

   excel
   =MID(A4, B4 + 1, LEN(A4) - B4)
   

   This will output example.com, because MID starts extracting right after the "@" symbol until the end of the string.

Summary

The FIND function in Excel is a powerful tool for locating the position of substrings within text strings. It is case-sensitive and allows specifying a starting position for the search. By combining FIND with other text functions, you can perform complex text manipulations and analyses in your Excel worksheets.