How to Use the FIND Function in Excel (7 Methods)

Method 1 – Extracting the Position of a Specified Character in a Text

In the following dataset, Column B contains text values, and Column C represents what is to be found in the texts in Column B. In Column D, we’ll extract the characters’ positions defined in Column C.

Steps:

• In Cell D5, the formula with the FIND function will be:

• Press Enter. The function returns 5, which means the letter ‘T’ lies in the 5th position in the text in Cell B5.
• Find the next two outputs in Cells D6 and D7.

Method 2 – Using the FIND Function for Case-Sensitive Character(s)

In cell D5, ‘t’ with lowercase cannot be found, so if you input ‘t’ as a find_text argument, you’ll be shown the #VALUE error. In cell D5, we’ve defined the find_text argument with ‘T’.

As a result, the function has returned an integer defining the position of ‘T’ in the text string in Cell B6.

Method 3 – Extracting a Text from the Beginning of a String up to a Certain Position 

The MID function returns the characters from the middle of a text string, given a starting position and length. The generic formula of this MID function is:

=MID(text, start_num, num_characters)

In Column B, some texts have spaces inside, and we have to extract the first names or words only.

• Enter the following formula in Cell C5:

• Press Enter. The word from the beginning of the text will be displayed in Cell B5.

In this formula, the starting_num for the MID function is 1, which means the output text will start with the first character in Cell B5. The FIND function defines the number of characters or character length up to the first space.

Method 4 – Drawing Out Text from the End of a String 

The RIGHT function returns the specified number of characters from the end of the text string whereas the LEN function returns the number of characters in a text string.

There are a few names in Column B, and we have to pull out the last names only in Column C.

• Enter the following formula in Cell C5:

• Press Enter to get the last name of Andy Robertson.

How Does the Formula Work?

➤ FIND function returns the position of the first space from the text in Cell B5 and that is 5.

➤ The LEN function returns the total number of characters from the text string in Cell B5, which is 14.

➤ The difference between the previous two outputs is the second argument for the RIGHT function, which represents the number of characters at the end of a text string.

➤ The RIGHT function extracts the last name defined by the number of characters from the end of that text string.

Method 5 – Finding the Position of a Character Multiple Times 

From Cell B5, we’ll find the positions of the 1st and 2nd spaces in the text string. To find the position of the first space,

• Enter the following formula in Cell C5:

To find the position of the second space in the text string lying in Cell B5,

• Enter the following formula Cell D5:

Read More: FIND Function Not Working in Excel 

Method 6 – Returning All Characters Between the 1st & 2nd Occurrences 

Column B has several texts with two spaces. For each case, we’ll extract the text between those two spaces in Column C.

• Enter the following formula in Cell C5:

• Press Enter, and the formula will return the extracted data between the two spaces from the text string in Cell B5.

How Does the Formula Work?

➤ In the second argument (start_num) of the MID function, the FIND function defines the starting number of the character as the position of the first space.

➤ FIND(” “, B5, FIND(” “,B5)+1) – FIND(” “,B5)-1; this part defines the number of characters before the second space from the starting position found in the previous step.

➤ The MID function returns the extracted text value based on the described arguments.

Method 7 – Pulling Out Text within Parenthesis 

In Column B, there are a few texts with parenthesis inside. We’ll extract the data lying inside the parentheses only.

• Enter the following formula with FIND and MID function in Cell C5:

• Press Enter, and you’ll get the text between parentheses at once.

How Does the Formula Work?

➤ In the second argument of the MID function, the FIND function defines the starting number for the MID function as the position of the ‘(‘ and by adding 1 to it.

➤ The third argument of the MID function, FIND(“)”,B5)-FIND(“(“,B5)-1, defines the number of characters between the opening and closing brackets.

➤ Finally, the MID function returns the extracted data or text within the parenthesis.

Things to Keep in Mind

If more than one character is included in the FIND function, it’ll return the position of the first character only.

FIND function does not support wildcard characters as it extracts the position of the defined text only in a numerical value.

As the FIND function is case-sensitive, the function will be unable to find the character if you include a text with the wrong case(s) as the find_text argument, thereby returning a #VALUE error.

To avoid case sensitivity, use the SEARCH function instead of the FIND function.

If the find_text argument is empty, the function will return 1.

If the input in the find_text argument is found more than once in the selected text string, the function will return the position of the first finding only.

Download the Practice Workbook

You can download the Excel workbook to practice.

<< Go Back to Excel Functions | Learn Excel